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

Source: Internet
Author: User

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 ";

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.