7 Coding PL/SQL Subprograms and Packages

Anonymous Blocks

An anonymous block is a PL/SQL program unit that has no name. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is invoked when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.

The following example of a PL/SQL anonymous block prints the names of all employees in department 20 in the hr.employees table by using the DBMS_OUTPUT package:

DECLARE
   Last_name    VARCHAR2(10);
   Cursor       c1 IS SELECT last_name 
                       FROM employees
                       WHERE department_id = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Last_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Last_name);
   END LOOP;
END;
/

Exceptions let you handle Oracle Database error conditions with PL/SQL program logic. This enables your application to prevent the server from issuing an error that can cause the client application to end. The following anonymous block handles the predefined Oracle Database exception NO_DATA_FOUND (which results in an ORA-01403 error if not handled):

DECLARE
   Emp_number   INTEGER := 9999;
   Emp_name     VARCHAR2(10);
BEGIN
   SELECT Ename INTO Emp_name FROM Emp_tab
      WHERE Empno = Emp_number;   -- no such number
   DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;

You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

DECLARE
   Emp_name           VARCHAR2(10);
   Emp_number         INTEGER;
   Empno_out_of_range EXCEPTION;
BEGIN
   Emp_number := 10001;
   IF Emp_number > 9999 OR Emp_number < 1000 THEN
      RAISE Empno_out_of_range;
   ELSE
      SELECT Ename INTO Emp_name FROM Emp_tab
         WHERE Empno = Emp_number;
      DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
END IF;
EXCEPTION
   WHEN Empno_out_of_range THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||
       ' is out of range.');
END;

Anonymous blocks are usually used interactively from a tool, such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are usually used to invoke stored subprograms or to open cursor variables.

Stored PL/SQL Program Units

A stored PL/SQL program unit is a subprogram (procedure or function) or package that:

• Has a name.

• Can take parameters, and can return values.

• Is stored in the data dictionary.

• Can be invoked by many users.

If a subprogram belongs to a package, it is called a package subprogram; if not, it is called a standalone subprogram.

Topics:

• Naming Subprograms

• Subprogram Parameters

• Creating Subprograms

• Altering Subprograms

• Dropping Subprograms and Packages

• External Subprograms

• Cross-Session PL/SQL Function Result Cache

• PL/SQL Packages

• PL/SQL Object Size Limits

• Creating Packages

• Naming Packages and Package Objects

• Package Invalidations and Session State

• Packages Supplied with Oracle Database

• Overview of Bulk Binding

• When to Use Bulk Binds

PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
   Emp_name       VARCHAR2(10);
   CURSOR         c1 (Depno NUMBER) IS
                     SELECT Ename FROM Emp_tab
                        WHERE deptno = Depno;
BEGIN
   OPEN c1(Dept_num);
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN C1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
   CLOSE c1;
END;

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)

Dept_number    number(2);
...
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- invoke for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
   RETURN Emp_tab%ROWTYPE IS ...

PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...

PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...

CREATE PROCEDURE Procedure_name AS   ...

CREATE PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                              Emp_ret     OUT Emp_tab%ROWTYPE) AS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;
/

SQL> @get_emp

DROP PROCEDURE Old_sal_raise;

CREATE PACKAGE BODY Employee_management AS
   FUNCTION Hire_emp (ename VARCHAR2, Job VARCHAR2,
      Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
      Deptno NUMBER) RETURN NUMBER IS
       New_empno    NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the invocation of this function.

   BEGIN

New_empno := Emp_sequence.NEXTVAL;

INSERT INTO emp VALUES (New_empno, ename, Job, Mgr,
         Hiredate, Sal, Comm, Deptno);
      RETURN (New_empno);
   END Hire_emp;

   PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

   BEGIN
      DELETE FROM emp WHERE Empno = Emp_id;
      IF SQL%NOTFOUND THEN
      Raise_application_error(-20011, 'Invalid Employee
         Number: ' || TO_CHAR(Emp_id));
   END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update 
-- salary with increase.

   BEGIN
      UPDATE emp
         SET Sal = Sal + Sal_incr
         WHERE Empno = Emp_id;
      IF SQL%NOTFOUND THEN
         Raise_application_error(-20011, 'Invalid Employee
            Number: ' || TO_CHAR(Emp_id));
      END IF;
   END Sal_raise;
END Employee_management;

SQL> CREATE SEQUENCE Emp_sequence
   > START WITH 8000 INCREMENT BY 10;

CREATE OR REPLACE PACKAGE Package_name AS ...

CREATE OR REPLACE PACKAGE BODY Package_name AS ...

ORA-04068: existing state of packages has been discarded

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN

-- Efficient method, using a bulk bind
   FORALL i IN Id.FIRST..Id.LAST   -- bulk-bind the VARRAY
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);

-- Slower method, running the UPDATE statements within a regular loop
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);
   END LOOP;
END;

FORALL i in Emp_Data.FIRST..Emp_Data.LAST
    INSERT INTO Emp_tab VALUES(Emp_Data(i));

-- Find all employees whose manager's ID number is 7698.
DECLARE
   TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
   Empno VAR_TAB;
   Ename VAR_TAB;
   Counter NUMBER;
   CURSOR C IS
      SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;
BEGIN

-- Efficient method, using a bulk bind
    SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
        FROM Emp_Tab WHERE Mgr = 7698;

-- Slower method, assigning each collection element within a loop.

   counter := 1;  
   FOR rec IN C LOOP  
      Empno(Counter) := rec.Empno;
      Ename(Counter) := rec.Ename;
      Counter := Counter + 1;
   END LOOP;
END; 

DECLARE
   TYPE Emplist IS VARRAY(100) OF NUMBER;
   Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698);
   TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE;
   Bonlist_inst BONLIST;
BEGIN
   Bonlist_inst := BONLIST(1,2,3,4,5);

   FORALL i IN Empids.FIRST..empIDs.LAST
      UPDATE Emp_tab SET Bonus = 0.1 * Sal
      WHERE Empno = Empids(i)
      RETURNING Sal BULK COLLECT INTO Bonlist;

   FOR i IN Empids.FIRST..Empids.LAST LOOP
      UPDATE Emp_tab Set Bonus = 0.1 * sal        
         WHERE Empno = Empids(i)
       RETURNING Sal INTO BONLIST(i);
   END LOOP;
END;

Declaring and Opening Cursor Variables

Memory is usually allocated for a cursor variable in the client application using the appropriate ALLOCATE statement. In Pro*C, use the EXEC SQL ALLOCATE cursor_name statement. In OCI, use the Cursor Data Area.

You can also use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.

Examples of Cursor Variables

This section includes several examples of cursor variable usage in PL/SQL. For additional cursor variable examples that use the programmatic interfaces, see the following:

• Pro*C/C++ Programmer's Guide

• Pro*COBOL Programmer's Guide

• Oracle Call Interface Programmer's Guide

Example 7-1 creates a package, Emp_data, that defines a PL/SQL cursor variable type, Emp_val_cv_type, and two procedures. The first procedure, Open_emp_cv, opens the cursor variable using a bind variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches rows from the Emp_tab table using the cursor variable. Example 7-2 invokes the Emp_data package procedures from a PL/SQL block.

CREATE OR REPLACE PACKAGE Emp_data AS
  TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
  PROCEDURE Open_emp_cv (Emp_cv          IN OUT Emp_val_cv_type,
                         Dept_number     IN     INTEGER); 
  PROCEDURE Fetch_emp_data (emp_cv       IN     Emp_val_cv_type,
                            emp_row      OUT    Emp_tab%ROWTYPE);
END Emp_data;

CREATE OR REPLACE PACKAGE BODY Emp_data AS
  PROCEDURE Open_emp_cv (Emp_cv      IN OUT Emp_val_cv_type,
                         Dept_number IN     INTEGER) IS
  BEGIN
    OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number;
  END open_emp_cv;
  PROCEDURE Fetch_emp_data (Emp_cv      IN  Emp_val_cv_type,
                            Emp_row     OUT Emp_tab%ROWTYPE) IS
  BEGIN
    FETCH Emp_cv INTO Emp_row;
  END Fetch_emp_data;
END Emp_data;

DECLARE
-- declare a cursor variable
   Emp_curs Emp_data.Emp_val_cv_type;
   Dept_number Dept_tab.Deptno%TYPE;
   Emp_row Emp_tab%ROWTYPE;

BEGIN
   Dept_number := 20;
-- open the cursor using a variable
   Emp_data.Open_emp_cv(Emp_curs, Dept_number);
-- fetch the data and display it
   LOOP
     Emp_data.Fetch_emp_data(Emp_curs, Emp_row);
     EXIT WHEN Emp_curs%NOTFOUND;
     DBMS_OUTPUT.PUT(Emp_row.Ename || '  ');
     DBMS_OUTPUT.PUT_LINE(Emp_row.Sal);
   END LOOP;
END;

The power of cursor variables comes from their ability to point to different cursors. Example 7-3 uses a discriminant to open a cursor variable to point to one of two different cursors.

CREATE OR REPLACE PACKAGE Emp_dept_data AS
  TYPE Cv_type IS REF CURSOR;
  PROCEDURE Open_cv (Cv          IN OUT cv_type,
                     Discrim     IN     POSITIVE); 
END Emp_dept_data;

CREATE OR REPLACE PACKAGE BODY Emp_dept_data AS
  PROCEDURE Open_cv (Cv      IN OUT cv_type,
                     Discrim IN     POSITIVE) IS
  BEGIN
    IF Discrim = 1 THEN
      OPEN Cv FOR SELECT * FROM Emp_tab WHERE Sal > 2000;
    ELSIF Discrim = 2 THEN
      OPEN Cv FOR SELECT * FROM Dept_tab;
    END IF;
  END Open_cv;
END Emp_dept_data;

You can invoke the Open_cv procedure in Example 7-3 to open the cursor variable and point it to a query on either the Emp_tab table or the Dept_tab table. Example 7-4 uses the cursor variable to fetch data and then uses the ROWTYPE_MISMATCH predefined exception to handle either fetch.

DECLARE
  Emp_rec  Emp_tab%ROWTYPE;
  Dept_rec Dept_tab%ROWTYPE;
  Cv       Emp_dept_data.CV_TYPE;

BEGIN
  Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch
  Fetch cv INTO Dept_rec;       -- but fetch into Dept_tab record
                                -- which raises ROWTYPE_MISMATCH
  DBMS_OUTPUT.PUT(Dept_rec.Deptno);
  DBMS_OUTPUT.PUT_LINE('  ' || Dept_rec.Loc);

EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE
           ('Row type mismatch, fetching Emp_tab data...');
      FETCH Cv INTO Emp_rec;
      DBMS_OUTPUT.PUT(Emp_rec.Deptno);
      DBMS_OUTPUT.PUT_LINE('  ' || Emp_rec.Ename);
    END;

Declaring Exceptions and Exception Handling Routines

User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application. When an exception is raised (signaled), the usual execution of the PL/SQL block stops, and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.

Application code can check for a condition that requires special attention using an IF statement. If there is an error condition, then two options are available:

• Enter a RAISE statement that names the appropriate exception. A RAISE statement stops the execution of the subprogram, and control passes to an exception handler (if any).

• Invoke the RAISE_APPLICATION_ERROR procedure to return a user-specified error number and message.

You can also define an exception handler to handle user-specified error messages. For example, Figure 7-1 shows the following:

• An exception and associated exception handler in a subprogram

• A conditional statement that checks for an error (such as transferring funds not available) and enters a user-specified error number and message within a trigger

• How user-specified error numbers are returned to the invoking environment (in this case, a subprogram), and how that application can define an exception that corresponds to the user-specified error number

Declare a user-defined exception in a subprogram or package body (private exceptions), or in the specification of a package (public exceptions). Define an exception handler in the body of a subprogram (standalone or package).

Figure 7-1 Exceptions and User-Defined Errors

Description of "Figure 7-1 Exceptions and User-Defined Errors"

Unhandled Exceptions

In database PL/SQL program units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a COMMIT statement before the point at which the unhandled exception is observed, then the implicit rollback of the program unit can only be completed back to the previous COMMIT.

Additionally, unhandled exceptions in database-stored PL/SQL program units propagate back to client-side applications that invoke the containing program unit. In such an application, only the application program unit invocation is rolled back (not the entire application program unit), because it is submitted to the database as a SQL statement.

If unhandled exceptions in database PL/SQL program units are propagated back to database applications, modify the database PL/SQL code to handle the exceptions. Your application can also trap for unhandled exceptions when invoking database program units and handle such errors appropriately.

Handling Errors in Distributed Queries

You can use a trigger or a stored subprogram to create a distributed query. This distributed query is decomposed by the local Oracle Database instance into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes run the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

If a portion of a distributed statement fails, possibly due to a constraint violation, then Oracle Database returns error number ORA-02055. Subsequent statements, or subprogram invocations, return error number ORA-02067 until a rollback or a rollback to savepoint is entered.

Design your application to check for any returned error messages that indicates that a portion of the distributed update has failed. If you detect a failure, rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.

Handling Errors in Remote Subprograms

When a subprogram is run locally or at a remote location, four types of exceptions can occur:

• PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION.

• PL/SQL predefined exceptions, such as NO_DATA_FOUND.

• SQL errors, such as ORA-00900 and ORA-02015.

• Application exceptions, which are generated using the RAISE_APPLICATION_ERROR procedure.

When using local subprograms, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

EXCEPTION
    WHEN ZERO_DIVIDE THEN
    /* Handle the exception */

Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

DECLARE
    ...
    Null_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(Null_salary, -20101);
BEGIN
    ...
    RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
    ...
EXCEPTION
    WHEN Null_salary THEN
        ...

When invoking a remote subprogram, exceptions are also handled by creating a local exception handler. The remote subprogram must return an error number to the local invoking subprogram, which then handles the exception, as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local subprogram, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.

PL/Scope

PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. Because PL/Scope is a compiler-driven tool, you use it through interactive development environments (such as SQL Developer and JDeveloper), rather than directly.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

For more information about PL/Scope, see Chapter 8, "Using PL/Scope".

PL/SQL Hierarchical Profiler

The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.

You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.

For a detailed description of PL/SQL hierarchical profiler, see Chapter 9, "Using the PL/SQL Hierarchical Profiler".

Oracle JDeveloper

Recent releases of Oracle JDeveloper have extensive features for debugging PL/SQL, Java, and multi-language programs. You can get Oracle JDeveloper as part of various Oracle product suites. Often, a more recent release is available as a download at http://www.oracle.com/technology/.

DBMS_OUTPUT Package

You can also debug stored subprograms and triggers using the Oracle Database package DBMS_OUTPUT. Put PUT and PUT_LINE statements in your code to output the value of variables and expressions to your terminal.

Privileges for Debugging PL/SQL and Java Stored Subprograms

Starting with Oracle Database 10g, a new privilege model applies to debugging PL/SQL and Java code running within the database. This model applies whether you are using Oracle JDeveloper, Oracle Developer, or any of the various third-party PL/SQL or Java development environments, and it affects both the DBMS_DEBUG and DBMS_DEBUG_JDWP APIs.

For a session to connect to a debugger, the effective user at the time of the connect operation must have the DEBUG CONNECT SESSION system privilege. This effective user might be the owner of a DR routine involved in making the connect call.

When a debugger becomes connected to a session, the session login user and the currently enabled session-level roles are fixed as the privilege environment for that debugging connection. Any DEBUG or EXECUTE privileges needed for debugging must be granted to that combination of user and roles.

• To be able to display and change Java public variables or variables declared in a PL/SQL package specification, the debugging connection must be granted either EXECUTE or DEBUG privilege on the relevant code.

• To be able to either display and change private variables or breakpoint and execute code lines step by step, the debugging connection must be granted DEBUG privilege on the relevant code

  Caution:

  The DEBUG privilege allows a debugging session to do anything that the subprogram being debugged could have done if that action had been included in its code.

In addition to these privilege requirements, the ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated. Use the PLSQL_DEBUG parameter and the DEBUG keyword on statements such as ALTER PACKAGE to control whether the PL/SQL compiler includes debug information in its results. If not, variables are not accessible, and neither stepping nor breakpoints stop on code lines. The PL/SQL compiler never generates debug information for code hidden with the PL/SQL wrap utility.

The DEBUG ANY PROCEDURE system privilege is equivalent to the DEBUG privilege granted on all objects in the database. Objects owned by SYS are included if the value of the O7_DICTIONARY_ACCESSIBILITY parameter is TRUE.

A debug role mechanism is available to carry privileges needed for debugging that are not normally enabled in the session. See the documentation on the DBMS_DEBUG and DBMS_DEBUG_JDWP packages for details on how to specify a debug role and any necessary related password.

The JAVADEBUGPRIV role carries the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges. Grant it only with the care those privileges warrant.

Writing Low-Level Debugging Code

If you are writing code for part of a debugger, you might need to use packages such as DBMS_DEBUG_JDWP or DBMS_DEBUG.

DBMS_DEBUG_JDWP Package

The DBMS_DEBUG_JDWP package, provided starting with Oracle9i Release 2, provides a framework for multi-language debugging that is expected to supersede the DBMS_DEBUG package over time. It is especially useful for programs that combine PL/SQL and Java.

DBMS_DEBUG Package

The DBMS_DEBUG package, provided starting with Oracle8i, implements server-side debuggers and provides a way to debug server-side PL/SQL program units. Several of the debuggers available, such as Oracle Procedure Builder and various third-party vendor solutions, use this API.

Privileges Required to Execute a Subprogram

If you are the owner of a standalone subprogram or package, then you can run the standalone subprogram or packaged subprogram, or any public subprogram or packaged subprogram at any time, as described in the previous sections. If you want to run a standalone or packaged subprogram owned by another user, then the following conditions apply:

• You must have the EXECUTE privilege for the standalone subprogram or package containing the subprogram, or you must have the EXECUTE ANY PROCEDURE system privilege. If you are executing a remote subprogram, then you must be granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not through a role.

• You must include the name of the owner in the invocation. For example:

  EXECUTE Jward.Fire_emp (1043);
  EXECUTE Jward.Hire_fire.Fire_emp (1043); 
  

• If the subprogram is a definer's-rights (DR) subprogram, then it runs with the privileges of the subprogram owner. The owner must have all the necessary object privileges for any referenced objects.

• If the subprogram is an invoker's-rights (IR) subprogram, then it runs with your privileges (as the invoker). In this case, you also need privileges on all referenced objects; that is, all objects accessed by the subprogram through external references that are resolved in your schema. You might hold these privileges directly or through a role. Roles are enabled unless an IR subprogram is invoked directly or indirectly by a DR subprogram.

CONNECT SYS/password AS SYSDBA;
CREATE USER Jward IDENTIFIED BY Jward;
GRANT CREATE ANY PACKAGE TO Jward;
GRANT CREATE ANY SESSION TO Jward;
GRANT EXECUTE ANY PROCEDURE TO Jward;
CONNECT SCOTT/password AS SYSDBA;

Invoking a Subprogram from a Trigger or Another Subprogram

A subprogram or trigger can invoke another stored subprogram. For example, included in the body of one subprogram might be the following line:

. . .
Sal_raise(Emp_id, 200);
. . .

This line invokes the Sal_raise procedure. Emp_id is a variable within the context of the procedure. Recursive subprogram invocations are allowed within PL/SQL: A subprogram can invoke itself.

Invoking a Subprogram Interactively from Oracle Database Tools

A subprogram can be invoked interactively from an Oracle Database tool, such as SQL*Plus. For example, to invoke a procedure named SAL_RAISE, owned by you, you can use an anonymous PL/SQL block, as follows:

BEGIN
   Sal_raise(7369, 200);
END;

An easier way to run a block is to use the SQL*Plus statement EXECUTE, which wraps BEGIN and END statements around the code you enter. For example:

EXECUTE Sal_raise(7369, 200);

Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:

VARIABLE Assigned_empno NUMBER

After defined, any session variable can be used for the duration of the session. For example, you might run a function and capture the return value using a session variable:

EXECUTE :Assigned_empno := Hire_emp('JSMITH', 'President', 
   1032, SYSDATE, 5000, NULL, 10);
PRINT Assigned_empno;
ASSIGNED_EMPNO
--------------
          2893

Invoking a Subprogram from a 3GL Application

A 3GL database application, such as a precompiler or an OCI application, can include an invocation to a subprogram within the code of the application.

To run a subprogram within a PL/SQL block in an application, simply invoke the subprogram. The following line within a PL/SQL block invokes the Fire_emp procedure:

Fire_emp1(:Empnun);

In this case, :Empno is a host (bind) variable within the context of the application.

To run a subprogram within the code of a precompiler application, you must use the EXEC call interface. For example, the following statement invokes the Fire_emp procedure in the code of a precompiler application:

EXEC SQL EXECUTE
   BEGIN
      Fire_emp1(:Empnum);
   END;
END-EXEC;

Remote Subprogram Invocations and Parameter Values

You must explicitly pass values to all remote subprogram parameters, even if there are defaults. You cannot access remote package variables and constants.

Referencing Remote Objects

Remote objects can be referenced within the body of a locally defined subprogram. The following procedure deletes a row from the remote employee table:

CREATE OR REPLACE PROCEDURE fire_emp(emp_id NUMBER) IS
BEGIN
    DELETE FROM emp@boston_server WHERE empno = emp_id;
END;

The following list explains how to properly invoke remote subprograms, depending on the invoking environment.

• Remote subprograms (standalone and packaged) can be invoked from within a subprogram, an OCI application, or a precompiler application by specifying the remote subprogram name, a database link, and the arguments for the remote subprogram.

  CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS
  BEGIN
    fire_emp1@boston_server(arg);
  END;
  

• In the previous example, you can create a synonym for FIRE_EMP1@BOSTON_SERVER. This enables you to invoke the remote subprogram from an Oracle Database tool application, such as a SQL*Forms application, as well from within a subprogram, OCI application, or precompiler application.

  CREATE SYNONYM synonym1 for  fire_emp1@boston_server;
  CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS
  BEGIN
    synonym1(arg);
  END;
  

• If you do not want to use a synonym, then you can write a local cover subprogram to invoke the remote subprogram.

  DECLARE
     arg NUMBER;
  BEGIN 
     local_procedure(arg); 
  END;
  

  Here, local_procedure is defined as in the first item of this list.

  See Also:

  "Synonyms for Subprograms and Packages"

  Caution:

  Unlike stored subprograms, which use compile-time binding, run-time binding is used when referencing remote subprograms. The user account to which you connect depends on the database link.

All invocations to remotely stored subprograms are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote subprogram is read-only). Furthermore, if a transaction that includes a remote subprogram invocation is rolled back, then the work done by the remote subprogram is also rolled back.

A subprogram invoked remotely can usually execute a COMMIT, ROLLBACK, or SAVEPOINT statement, the same as a local subprogram. However, there are some differences in action:

• If the transaction was originated by a database that is not an Oracle database, as might be the case in XA applications, these operations are not allowed in the remote subprogram.

• After doing one of these operations, the remote subprogram cannot start any distributed transactions of its own.

• If the remote subprogram does not commit or roll back its work, the commit is done implicitly when the database link is closed. In the meantime, further invocations to the remote subprogram are not allowed because it is still considered to be performing a transaction.

A distributed update modifies data on two or more databases. A distributed update is possible using a subprogram that includes two or more remote updates that access data on different databases. Statements in the construct are sent to the remote databases, and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, then a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating subprograms that perform distributed updates.

Pay special attention when using a local subprogram that invokes a remote subprogram. If a timestamp mismatch is found during execution of the local subprogram, then the remote subprogram is not run, and the local subprogram is invalidated.

Synonyms for Subprograms and Packages

Synonyms can be created for standalone subprograms and packages to do the following:

• Hide the identity of the name and owner of a subprogram or package.

• Provide location transparency for remotely stored subprograms (standalone or within a package).

When a privileged user needs to invoke a subprogram, an associated synonym can be used. Because the subprograms defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual subprograms within a package.

For more information about synonyms, see Oracle Database Concepts.

Why Invoke Stored PL/SQL Subprograms from SQL Statements?

Invoking PL/SQL subprograms in SQL statements enables you to do the following:

• Increase user productivity by extending SQL.

  Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.

• Increase query efficiency.

  Functions used in the WHERE clause of a query can filter data using criteria that must otherwise be evaluated by the application.

• Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).

• Provide parallel query execution.

  If the query is parallelized, then SQL statements in your PL/SQL subprogram might also be run in parallel (using the parallel query option).

Where PL/SQL Functions Can Appear in SQL Statements

A PL/SQL function can appear in a SQL statement wherever a built-in SQL function or an expression can appear in a SQL statement. For example, a PL/SQL function can appear in the following:

• Select list of the SELECT statement

• Condition of the WHERE or HAVING clause

• CONNECT BY, START WITH, ORDER BY, or GROUP BY clause

• VALUES clause of the INSERT statement

• SET clause of the UPDATE statement

A PL/SQL table function (which returns a collection of rows) can appear in a SELECT statement in place of the following:

• Column name in the SELECT list

• Table name in the FROM clause

A PL/SQL function cannot appear in the following contexts, which require unchanging definitions:

• CHECK constraint clause of a CREATE or ALTER TABLE statement

• Default value specification for a column

When PL/SQL Functions Can Appear in SQL Expressions

To be invoked from a SQL expression, a PL/SQL function must satisfy the following additional requirements:

• It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.

• Its formal parameters must be IN parameters, not OUT or IN OUT parameters.

• Its formal parameters and its result value (if any) must have Oracle built-in datatypes (such as CHAR, DATE, or NUMBER), not PL/SQL datatypes (such as BOOLEAN, RECORD, or TABLE).

  There is an exception to this rule: A formal parameter can have a PL/SQL datatype if the corresponding actual parameter is implicitly converted to the datatype of the formal parameter (as in Example 7-6).

The function in Example 7-5 satisfies the preceding requirements. It uses the table payroll:

CREATE TABLE payroll (srate  NUMBER,
                      orate  NUMBER,
                      acctno NUMBER);

CREATE FUNCTION gross_pay (emp_id IN NUMBER,
                           st_hrs IN NUMBER DEFAULT 40,
                           ot_hrs IN NUMBER DEFAULT 0)
  RETURN NUMBER IS
  st_rate  NUMBER;
  ot_rate  NUMBER;

BEGIN 
   SELECT srate, orate INTO st_rate, ot_rate FROM payroll
      WHERE acctno = emp_id;
   RETURN st_hrs * st_rate + ot_hrs * ot_rate;
END gross_pay;

In the SQL*Plus script in Example 7-6, the SQL statement CALL invokes the PL/SQL function f1, whose formal parameter has PL/SQL datatype PLS_INTEGER. The CALL statement succeeds because the actual parameter, 2, is implicitly converted to the datatype PLS_INTEGER. If the actual parameter had a value outside the range of PLS_INTEGER, the CALL statement would fail.

CREATE OR REPLACE FUNCTION f1 (b IN PLS_INTEGER)
  RETURN PLS_INTEGER IS
BEGIN
  RETURN
    CASE
      WHEN b > 0 THEN 1
      WHEN b <= 0 THEN -1
      ELSE NULL
    END;
END f1;
/
VARIABLE x NUMBER
CALL f1(b=>2) INTO :x
/
PRINT x
1

Controlling Side Effects

The purity of a stored subprogram refers to the side effects of that subprogram on database tables or package variables. Side effects can prevent the parallelization of a query, yield order-dependent (and therefore, indeterminate) results, or require that package state be maintained across user sessions. Various side effects are not allowed when a function is invoked from a SQL query or DML statement.

In releases prior to Oracle8i, Oracle Database leveraged the PL/SQL compiler to enforce restrictions during the compilation of a stored subprogram or a SQL statement. Starting with Oracle8i, the compile-time restrictions were relaxed, and a smaller set of restrictions are enforced during execution.

This change provides uniform support for stored subprograms written in PL/SQL, Java, and C, and it allows programmers the most flexibility possible.

Topics:

• Restrictions

• Declaring a Function

• Parallel Query and Parallel DML

• PRAGMA RESTRICT_REFERENCES for Backward Compatibility

CREATE FUNCTION F1 (P1 NUMBER) RETURN NUMBER DETERMINISTIC IS 
BEGIN 
  RETURN P1 * 2; 
END; 

CREATE FUNCTION F1 (P1 NUMBER) RETURN NUMBER PARALLEL_ENABLE IS 
BEGIN 
  RETURN P1 * 2; 
END; 

PRAGMA RESTRICT_REFERENCES ( 
    Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] ); 

CREATE TABLE Accts (
 Yrs      NUMBER,
 Amt      NUMBER,
 Acctno   NUMBER,
 Rte      NUMBER);

CREATE PACKAGE Finance AS  -- package specification 
   FUNCTION Compound 
         (Years  IN NUMBER, 
          Amount IN NUMBER, 
          Rate   IN NUMBER) RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (Compound, WNDS, WNPS, RNDS, RNPS); 
END Finance; 
 
CREATE PACKAGE BODY Finance AS  --package body 
   FUNCTION Compound 
         (Years  IN NUMBER, 
          Amount IN NUMBER, 
          Rate   IN NUMBER) RETURN NUMBER IS 
   BEGIN 
      RETURN Amount * POWER((Rate / 100) + 1, Years); 
   END Compound; 
                   -- no pragma in package body 
END Finance; 

DECLARE
   Interest NUMBER;
   Acct_id NUMBER;
BEGIN 
   SELECT Finance.Compound(Yrs, Amt, Rte)  -- function invocation
   INTO   Interest       
   FROM   Accounts       
   WHERE  Acctno = Acct_id; 

CREATE OR REPLACE PACKAGE P1 IS 
   FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS 
      LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; 
      PRAGMA RESTRICT_REFERENCES(F1,WNDS,TRUST); 
   FUNCTION F2 (P1 NUMBER) RETURN NUMBER; 

   PRAGMA RESTRICT_REFERENCES(F2,WNDS); 
END; 
 
CREATE OR REPLACE PACKAGE BODY P1 IS 
   FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS 
   BEGIN 
      RETURN F1(P1); 
   END; 
END;   

CREATE OR REPLACE PACKAGE P1a IS 
   FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS 
      LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; 
   FUNCTION F2 (P1 NUMBER) RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES(F2,WNDS,TRUST); 
END; 
 
CREATE OR REPLACE PACKAGE BODY P1a IS 
   FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS 
   BEGIN 
      RETURN F1(P1); 
   END; 
END; 
 

INSERT INTO my_table values(3, 'SCOTT'); 

UPDATE my_table SET id=777 WHERE name='SCOTT';

CREATE PACKAGE Tests AS 
    FUNCTION Valid (x NUMBER) RETURN CHAR; 
    FUNCTION Valid (x DATE) RETURN CHAR; 
    PRAGMA RESTRICT_REFERENCES (valid, WNDS); 
 END;

Serially Reusable PL/SQL Packages

PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE (using pragma syntax).

For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).

The unit of work for serially reusable packages is implicitly a call to the server; for example, an OCI call to the server, or a PL/SQL RPC call from a client to a server, or an RPC call from a server to another server.

Topics:

• Package States

• Why Serially Reusable Packages?

• Syntax of Serially Reusable Packages

• Semantics of Serially Reusable Packages

• Examples of Serially Reusable Packages

PRAGMA SERIALLY_REUSABLE;
 

CONNECT SCOTT/password

CREATE OR REPLACE PACKAGE Sr_pkg IS 
  PRAGMA SERIALLY_REUSABLE; 
  N NUMBER := 5;                -- default initialization 
END Sr_pkg; 

CONNECT SCOTT/password

# first CALL to server 
BEGIN 
   Sr_pkg.N := 10; 
END; 

# second CALL to server 
BEGIN 
   DBMS_OUTPUT.PUT_LINE(Sr_pkg.N); 
END; 

5 

CONNECT SCOTT/password

DROP PACKAGE Sr_pkg;
CREATE OR REPLACE PACKAGE Sr_pkg IS
   PRAGMA SERIALLY_REUSABLE;
   TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
   Num     NUMBER        := 10;
   Str     VARCHAR2(200) := 'default-init-str';
   Str_tab STR_TABLE_TYPE;
   
    PROCEDURE Print_pkg;
    PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);
END Sr_pkg;
CREATE OR REPLACE PACKAGE BODY Sr_pkg IS
   -- the body is required to have the pragma because the
  -- specification of this package has the pragma
  PRAGMA SERIALLY_REUSABLE;
   PROCEDURE Print_pkg IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);
      DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);
      DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);
      FOR i IN 1..Sr_pkg.Str_tab.Count LOOP
         DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i));
      END LOOP;
   END;
   PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
   BEGIN
   -- init the package globals
      Sr_pkg.Num := N;
      Sr_pkg.Str := V;
      FOR i IN 1..n LOOP
         Sr_pkg.Str_tab(i) := V || ' ' || i;
   END LOOP;
   -- print the package
   Print_pkg;
   END;
 END Sr_pkg;

SET SERVEROUTPUT ON;

Rem SR package access in a CALL:

BEGIN
   -- initialize and print the package
   DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');
   Sr_pkg.Init_and_print_pkg(4, 'abracadabra');
   -- print it in the same call to the server.
   -- we should see the initialized values.
   DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');
   Sr_pkg.Print_pkg;
END;

Initing and printing pkg state..
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL...
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4

REM SR package access in subsequent CALL:
BEGIN
   -- print the package in the next call to the server.
   -- The package state should be reset to the initial (default) values.
   DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');
   Sr_pkg.Print_pkg;
END;
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0

REM  For serially reusable pkg: At the end work boundaries
REM  (which is currently the OCI call boundary) all open
REM  cursors will be closed.
REM
REM  Because the cursor is closed - every time we fetch we 
REM  will start at the first row again.

CONNECT SCOTT/password
DROP PACKAGE  Sr_pkg;
DROP TABLE People;
CREATE TABLE People (Name VARCHAR2(20));
INSERT INTO  People  VALUES ('ET');
INSERT INTO  People  VALUES ('RAMBO');
CREATE OR REPLACE PACKAGE Sr_pkg IS
   PRAGMA SERIALLY_REUSABLE;
   CURSOR C IS SELECT Name FROM People;
END Sr_pkg;
SQL> SET SERVEROUTPUT ON;
SQL> 
CREATE OR REPLACE PROCEDURE Fetch_from_cursor IS
Name VARCHAR2(200);
BEGIN
   IF (Sr_pkg.C%ISOPEN) THEN
      DBMS_OUTPUT.PUT_LINE('cursor is already open.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('cursor is closed; opening now.');
      OPEN Sr_pkg.C;
   END IF;
   -- fetching from cursor.
   FETCH sr_pkg.C INTO name;
   DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
   FETCH Sr_pkg.C INTO name;
   DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
   -- Oops forgot to close the cursor (Sr_pkg.C).
   -- But, because it is a Serially Reusable pkg's cursor, 
   -- it will be closed at the end of this CALL to the server.
END;
EXECUTE fetch_from_cursor;
cursor is closed; opening now.
fetched: ET
fetched: RAMBO