Fixed_date, empowering Skills, procedure execution, PL/SQL Note the place

This article discusses 4 points of knowledge,

1. Fixed_date Parameters

2. Empowering Skills

3. How the procedure executes

4. Several areas to be aware of in PL/SQL

Fixed_date parameters

The customer wants to modify Oracle's system time for testing, but Oracle's default system time is to read the system time of its server, so it cannot be modified arbitrarily. However, the Fixed_date parameter can be modified to allow Sysdate to return the specified value in Oracle for easy testing. The command is as follows:

Set the time

Alter system set fixed_date= ' 2014-06-20-14:23:33 ';

Cancel Settings

alter system set Fixed_date=none;

Empowering Skills

This command requires ALTER system permission, so if you want the normal user to execute this command, you have to assign the alter system to the normal user, so we can assign the right

First create a procedure

Create or Replace procedureSet_fixed_time (V_time dba_objects.object_name%TYPE) asbegin    ExecuteImmediate'alter system set fixed_date='||Chr the)||V_time||Chr the) ;End;/

Then let the ordinary user have the Execute permission of the procedure and synonyms

SQL>Create for set_fixed_time; Synonym created. SQL>Grantexecute on to MyUser; Grant succeeded.

This allows the average user to set the time.

How the procedure executes

One of the points to note here is that the normal user myuser does not have alter system permissions, but can run procedure set_fixed_time This is because procedure is executing with its defined permissions.

Several areas to be aware of in PL/SQL

PL/SQL code has many strange specifications, and of course it may be that these specifications have specific meanings. Listed here, if you do not know, in the writing of PL/SQL will be a headache.

Create or Replace procedureSet_fixed_time (V_time dba_objects.object_name%TYPE) asbegin    ExecuteImmediate'alter system set fixed_date='||Chr the)||V_time||Chr the) ;End;/

The type definition for v_time in this code must be in the form of a table name.%type, if you use the direct type VARCHAR2 (800), an error occurs.

The Execute SQL statement must be executed with execute immediate sql_statement. Sql_statement has the following requirements.

1. Must be "enclosed"

2. Normal sql_statement may be with, but cannot be added in this ";