CREATE PROCEDURE

The CREATE PROCEDURE statement creates a standalone stored procedure.

Required Privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE [OR REPLACE] PROCEDURE [Owner.]ProcedureName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     [InvokerRightsClause][AccessibleByClause] [DETERMINISTIC]
     {IS|AS} plsql_procedure_body

InvokerRightsClause::=
AUTHID {CURRENT_USER|DEFINER}

AccessibleByClause::=
ACCESSIBLE BY(accessor[,...])

accessor::= 
[UnitKind][Owner.]UnitName

You can specify InvokerRightsClause, AccessibleByClause, or DETERMINISTIC in any order.

Parameters

Parameter	Description
OR REPLACE	Specify OR REPLACE to recreate the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping and recreating it. When you recreate a procedure, TimesTen recompiles it.
ProcedureName	Name of procedure.
arguments	Name of argument/parameter. You can specify 0 or more parameters for the procedure. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type.
[IN|OUT|IN OUT]	Parameter modes. IN is a read-only parameter. You can pass the parameter's value into the procedure but the procedure cannot pass the parameter's value out of the procedure and back to the calling PL/SQL block.The value of the parameter cannot be changed. OUT is a write-only parameter. Use an OUT parameter to pass a value back from the procedure to the calling PL/SQL block. You can assign a value to the parameter. IN OUT is a read/write parameter. You can pass values into the procedure and return a value back to the calling program (either the original, unchanged value or a new value set within the procedure. IN is the default.
NOCOPY	Specify NOCOPY to instruct TimesTen to pass the parameter as fast as possible. Can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed NOCOPY.
DEFAULT expr	Use this clause to specify a DEFAULT value for the parameter. You can specify := in place of the keyword DEFAULT.
InvokerRightsClause	Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows: Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default. Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it. For more information, see Definer's Rights and Invoker's Rights (AUTHID Clause) in the Oracle TimesTen In-Memory Database Security Guide.
AccessibleByClause	Use this clause to specify one or more accessors (PL/SQL units) that can invoke the procedure directly. The list of accessors that can access the procedure is called a white list. A white list gives you the ability to add an extra layer of security to your PL/SQL objects. Specifically, you can restrict access to the procedure to only those objects on the white list. The AccessibleByClause can appear only once in the CREATE PROCEDURE statement. Syntax: ACCESSIBLE BY (accessor [,...])
accessor	Used in the AccessibleByClause. An accessor is a PL/SQL unit that can invoke the procedure. An accessor can appear more than once in the AccessibleByClause. Syntax: [UnitKind][Owner.]UnitName
UnitKind	Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the kind of PL/SQL unit that can invoke the procedure. UnitKind is optional, but if specified, valid options are: FUNCTION PROCEDURE PACKAGE
[Owner.]UnitName	Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the name of the PL/SQL unit that can invoke the procedure. If you specify UnitKind, then UnitName must be a name of a unit of that kind. For example, if you specify PROCEDURE for UnitKind, then UnitName must be the name of a procedure. UnitName is required. You can optionally specify Owner. If you specify Owner, then UnitName must reside in that owner's schema. If you do not specify Owner, UnitName must be in the schema that contains the procedure.
DETERMINISTIC	Specify DETERMINISTIC to indicate that the procedure returns the same result value whenever it is called with the same values for its parameters.
IS|AS	Specify either IS or AS to declare the body of the procedure.
plsql_procedure_body	Specifies the procedure body.

Description

• AccessibleByClause:

  • The compiler checks the validity of the syntax of the AccessibleByClause, but does not check that the accessor exists. Therefore, you can define an accessor that does yet exist in the owner's schema.

  • When you invoke the procedure, the compiler first does the normal permission checks on the invocation. If any check fails, the invocation fails, even if the invoker is an accessor. If all normal permission checks on the invocation succeed, and the procedure has no AccessibleByClause, the invocation succeeds. If the procedure has an AccessibleByClause, the invocation succeeds only if the invoker is an accessor.

• When you create or replace a procedure, the privileges granted on the procedure remain the same. If you drop and recreate the object, the object privileges that were granted on the original object are revoked.

• The namespace for PL/SQL procedures is distinct from the TimesTen built-in procedures. You can create a PL/SQL procedure with the same name as a TimesTen built-in procedure.

• TimesTen does not support:

  • call_spec clause

  • AS EXTERNAL clause

• In a replicated environment, the CREATE PROCEDURE statement is not replicated. For more information, see Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in the Oracle TimesTen In-Memory Database Replication Guide.

Examples

Using the Accessible By Clause

This example creates the ProtectedProc procedure and uses the ACCESSIBLE BY clause to restrict access to the CallingProc procedure. The CallingProc procedure does not yet exist. The example then creates the CallingProc procedure, which calls the ProtectedProc procedure. The CallingProc procedure is successfully created, as it is specified in the ACCESSIBLE BY clause. The example then attempts to call the ProtectedProc procedure directly, resulting in an error. It concludes with attempting to create the AnotherCallingProc procedure that references the ProtectedProc procedure, but the AnotherCallingProc procedure is not in the white list. A compilation error results.

Steps to illustrate the example:

1. Create the ProtectedProc procedure, specifying the ACCESSIBLE BY clause. The CallingProc procedure is in the white list. It does not yet exist.

   Command> CREATE OR REPLACE PROCEDURE ProtectedProc
              ACCESSIBLE BY (CallingProc)
            AS
            BEGIN
              DBMS_OUTPUT.PUT_LINE ('ProtectedProc');
            END;
            /
             
   Procedure created.
   

2. Create the CallingProc procedure, referencing the ProtectedProc procedure.

   Command> CREATE OR REPLACE PROCEDURE CallingProc
            AS
            BEGIN
              DBMS_OUTPUT.PUT_LINE ('CallingProc');
              ProtectedProc;
            END;
            /
            
   Procedure created.
   

3. Call the CallingProc procedure. The procedure is successfully executed.

   Command> SET SERVEROUTPUT ON
   Command> exec CallingProc;
   CallingProc
   ProtectedProc
    
   PL/SQL procedure successfully completed.
   

4. Attempt to call the ProtectedProc procedure directly. An error is thrown due to insufficient access privileges.

   Command> exec ProtectedProc;
    8503: ORA-06550: line 1, column 7:
   PLS-00904: insufficient privilege to access object PROTECTEDPROC
    8503: ORA-06550: line 1, column 7:
   PL/SQL: Statement ignored
   The command failed.
   

5. Create the AnotherCallingProc procedure that references the ProtectedProc procedure. The AnotherCallingProc is not in the white list (not listed in the ACCESSIBLE BY clause of ProtectedProc), resulting in a compilation error.

   Command> CREATE OR REPLACE PROCEDURE AnotherCallingProc
            AS
            BEGIN
              DBMS_OUTPUT.PUT_LINE ('AnotherCallingProc');
              ProtectedProc;
            END;
            /
    
   Warning: Procedure created with compilation errors.
    
   Command> SHOW ERRORS
   Errors for PROCEDURE ANOTHERCALLINGPROC:
    
   LINE/COL ERROR
   -------- -----------------------------------------------------------------
   5/1      PL/SQL: Statement ignored
   5/1      PLS-00904: insufficient privilege to access object PROTECTEDPROC
   

Using the Accessor Clause

This example illustrates the uses of the accessor clause through a sequence of steps.

1. Create the SampleUser1 and SampleUser2 users and grant ADMIN privileges to both users.

   Command> CREATE USER SampleUser1 IDENTIFIED BY SampleUser1;
    
   User created.
    
   Command> CREATE USER SampleUser2 IDENTIFIED BY SampleUser2;
    
   User created.
   Command> GRANT ADMIN TO SampleUser1, SampleUser2;
   

2. Create the SampleUser1.ProtectedProc procedure, specifying the ACCESSIBLE BY clause. The CallingProc procedure is specified in the white list without an owner. The owner of the CallingProc procedure is assumed to be in the same schema as the owner of the procedure with the ACCESSIBLE BY clause. Thus, CallingProc is assumed to be in the SampleUser1 schema.

   Command> CREATE OR REPLACE PROCEDURE SampleUser1.ProtectedProc
            ACCESSIBLE BY (CallingProc)
            AS
            BEGIN
              DBMS_OUTPUT.PUT_LINE ('SampleUser1 ProtectedProc');
            END;
            /
    
   Procedure created.
   

3. Connect as SampleUser1. Create the CallingProc procedure, referencing the SampleUser1.ProtectedProc procedure.

   Command> Connect adding "uid=SampleUser1;pwd=SampleUser1PW" as SampleUser1;
   Connection successful:
   DSN=database1;UID=SampleUser1;DataStore=/scratch/sampleuser1/database1;
   DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
   PermSize=128;
   (Default setting AutoCommit=1)
   
   sampleuser1: Command> CREATE OR REPLACE PROCEDURE CallingProc
                         AS
                         BEGIN
                           DBMS_OUTPUT.PUT_LINE ('SampleUser1 CallingProc');
                           ProtectedProc;
                         END;
                         /
    
   Procedure created.
   

4. From the SampleUser1 connection, call the CallingProc procedure. The call succeeds.

   sampleuser1: Command> SET SERVEROUTPUT ON
   sampleuser1: Command> exec CallingProc;
   SampleUser1 CallingProc
   SampleUser1 ProtectedProc
    
   PL/SQL procedure successfully completed.
   

5. Connect to SampleUser2. Create the CallingProc procedure, referencing the SampleUser1.ProtectedProc procedure. A compilation error results.

   SampleUser1: Command> connect adding "uid=Sampleuser2;pwd=SampleUser2PW"
                         as SampleUser2;
   Connection successful:
   DSN=database1;UID=Sampleuser2;DataStore=/scratch/sampleuser2/database1;
   DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
   PermSize=128;
   (Default setting AutoCommit=1)
   
   sampleuser2: Command> CREATE OR REPLACE PROCEDURE CallingProc
                         AS
                         BEGIN
                           DBMS_OUTPUT.PUT_LINE ('SampleUser2 CallingProc');
                           SampleUser1.ProtectedProc;
                         END;
                         /
    
   Warning: Procedure created with compilation errors.
    
   sampleuser2: Command> SHOW ERRORS
   Errors for PROCEDURE CALLINGPROC:
    
   LINE/COL ERROR
   -------- -----------------------------------------------------------------
   5/1      PL/SQL: Statement ignored
   5/1      PLS-00904: insufficient privilege to access object PROTECTEDPROC
   

6. Switch to the SampleUser1 connection. Recreate the ProtectedProc procedure.

   sampleuser2: Command> use SampleUser1
   sampleuser1: Command> CREATE OR REPLACE PROCEDURE ProtectedProc
                         ACCESSIBLE BY (CallingProc, SampleUser2.CallingProc)
                         AS
                         BEGIN
                           DBMS_OUTPUT.PUT_LINE ('SampleUser1 ProtectedProc');
                         END;
                         /
    
   Procedure created.
   

7. From the SampleUser2 connection, call the CallingProc procedure. The SampleUser2.CallingProc is in the white list of the SampleUser1.ProtectedProc procedure, resulting in successful execution.

   sampleuser1: Command> use SampleUser2;
   sampleuser2: Command> SET SERVEROUTPUT ON
   sampleuser2: Command> exec CallingProc
   SampleUser2 CallingProc
   SampleUser1 ProtectedProc
    
   PL/SQL procedure successfully completed.

Using the CREATE PROCEDURE Statement to Retrieve Information

Create a procedure query_emp to retrieve information about an employee. Pass the employee_id 171 to the procedure and retrieve the last_name and salary into two OUT parameters.

Command> CREATE OR REPLACE PROCEDURE query_emp
                   (p_id IN employees.employee_id%TYPE,
                    p_name  OUT employees.last_name%TYPE,
                    p_salary OUT employees.salary%TYPE) IS
                 BEGIN
                   SELECT last_name, salary INTO p_name, p_salary
                   FROM employees
                   WHERE employee_id = p_id;
                 END query_emp;
                 /
 
Procedure created.