6 Coding PL/SQL Subprograms and Packages

Anonymous Blocks

An anonymous block is a PL/SQL 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.

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.

The anonymous block in Example 6-1 uses the DBMS_OUTPUT package to print the names of all employees in the HR.EMPLOYEES table who are in department 20.

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

Hartstein
Fay

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

DECLARE
  Emp_number  INTEGER := 9999
  Emp_name    VARCHAR2(10);
BEGIN
  SELECT LAST_NAME INTO Emp_name
    FROM EMPLOYEES
      WHERE EMPLOYEE_ID = Emp_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;
/

No such employee: 9999

You can also define your own exceptions; that is, you can declare them in the declaration part of a block and define them in the exception part of the block, as in Example 6-3.

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 LAST_NAME INTO Emp_name
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = 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;
/

Employee number 10001 is out of range.

Stored PL/SQL Units

A stored PL/SQL 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

• 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

CREATE OR REPLACE PROCEDURE get_emp_names (
  dept_num IN NUMBER
)
IS
  emp_name  VARCHAR2(10);
  CURSOR    c1 (dept_num NUMBER) IS
                SELECT LAST_NAME FROM EMPLOYEES
                WHERE DEPARTMENT_ID = dept_num;
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 EMPLOYEES.DEPARTMENT_ID%TYPE)

dept_number  NUMBER(2);
...
PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);

CREATE OR REPLACE PROCEDURE get_emp_rec (
  emp_number  IN  EMPLOYEES.EMPLOYEE_ID%TYPE,
  emp_info    OUT EMPLOYEES%ROWTYPE
)
IS
BEGIN
  SELECT * INTO emp_info
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_number;
END;
/
 

DECLARE
  emp_row  EMPLOYEES%ROWTYPE;
BEGIN
  get_emp_rec(206, emp_row);
  DBMS_OUTPUT.PUT('EMPLOYEE_ID: ' || emp_row.EMPLOYEE_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('FIRST_NAME: ' || emp_row.FIRST_NAME);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('LAST_NAME: ' || emp_row.LAST_NAME);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('EMAIL: ' || emp_row.EMAIL);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('PHONE_NUMBER: ' || emp_row.PHONE_NUMBER);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('HIRE_DATE: ' || emp_row.HIRE_DATE);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('JOB_ID: ' || emp_row.JOB_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('SALARY: ' || emp_row.SALARY);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('COMMISSION_PCT: ' || emp_row.COMMISSION_PCT);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('MANAGER_ID: ' || emp_row.MANAGER_ID);
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT('DEPARTMENT_ID: ' || emp_row.DEPARTMENT_ID);
  DBMS_OUTPUT.NEW_LINE;
END;
/

EMPLOYEE_ID: 206
FIRST_NAME: William
LAST_NAME: Gietz
EMAIL: WGIETZ
PHONE_NUMBER: 415.555.0100
HIRE_DATE: 07-JUN-94
JOB_ID: AC_ACCOUNT
SALARY: 8300
COMMISSION_PCT:
MANAGER_ID: 205
DEPARTMENT_ID: 110

FUNCTION get_emp_rec (dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE)
   RETURN EMPLOYEES%ROWTYPE IS ...

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

@get_emp

DROP PROCEDURE Old_sal_raise;

-- Sequence that packaged function needs:
 
CREATE SEQUENCE emp_sequence
START WITH 8000
INCREMENT BY 10;
 
-- Package specification:
 
CREATE or REPLACE PACKAGE employee_management IS
  FUNCTION hire_emp (
  firstname  VARCHAR2,
  lastname   VARCHAR2,
  email      VARCHAR2,
  phone      VARCHAR2,
  hiredate   DATE,
  job        VARCHAR2,
  sal        NUMBER,
  comm       NUMBER,
  mgr        NUMBER,
  deptno     NUMBER
) RETURN NUMBER;
 
 PROCEDURE fire_emp(
    emp_id IN NUMBER
 );
 
 PROCEDURE sal_raise (
    emp_id IN NUMBER,
    sal_incr IN NUMBER
 );
END employee_management;
/
 
-- Package body:
 
CREATE or REPLACE PACKAGE BODY employee_management IS
  FUNCTION hire_emp (
    firstname  VARCHAR2,
    lastname   VARCHAR2,
    email      VARCHAR2,
    phone      VARCHAR2,
    hiredate   DATE,
    job        VARCHAR2,
    sal        NUMBER,
    comm       NUMBER,
    mgr        NUMBER,
    deptno     NUMBER
  ) RETURN NUMBER
 IS
   new_empno  NUMBER(10);
 BEGIN

new_empno := emp_sequence.NEXTVAL;
 
    INSERT INTO EMPLOYEES (
      employee_id,
      first_name,
      last_name,
      email,
      phone_number,
      hire_date,
      job_id,
      salary,
      commission_pct,
      manager_id,
      department_id
    )
    VALUES (
      new_empno,
      firstname,
      lastname,
      email,
      phone,
      hiredate,  
      job,
      sal, 
      comm,
      mgr,
      deptno
    );

   RETURN (new_empno);
 END hire_emp;
 
 PROCEDURE fire_emp (
   emp_id IN NUMBER
 ) IS
 BEGIN
   DELETE FROM EMPLOYEES
   WHERE EMPLOYEE_ID = 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
  ) IS
  BEGIN
    UPDATE EMPLOYEES
    SET SALARY = SALARY + sal_incr
    WHERE EMPLOYEE_ID = 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;
/

DECLARE
  empno  NUMBER(6);
  sal    NUMBER(6);
  temp   NUMBER(6);
BEGIN
  empno := employee_management.hire_emp(
            'John',
            'Doe',
            'john.doe@company.com',
            '555-0100',
            '20-SEP-07',
            'ST_CLERK',
            2500,
            0,
            100,
            20);
 
  DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno));
END;
/

CREATE OR REPLACE PACKAGE Package_name AS ...

CREATE OR REPLACE PACKAGE BODY Package_name AS ...

PROCEDURE p IS
  package_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT (package_exception, -6508);
BEGIN
 ...
EXCEPTION
  WHEN package_exception THEN
    RAISE;
END;
/

PROCEDURE p IS
  package_exception EXCEPTION;
  other_exception   EXCEPTION;
  PRAGMA EXCEPTION_INIT (package_exception, -6508);
  PRAGMA EXCEPTION_INIT (other_exception, -20001);
BEGIN
 ...
EXCEPTION
  WHEN package_exception THEN
    ...
    RAISE other_exception;
END;
/

DECLARE
  2    TYPE numlist IS VARRAY (100) OF NUMBER;
  3    id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
  -- Efficient method, using bulk bind:
  
  FORALL i IN id.FIRST..id.LAST
  UPDATE EMPLOYEES
  SET SALARY = 1.1 * SALARY
  WHERE MANAGER_ID = id(i);
 
 -- Slower method:
 
 FOR i IN id.FIRST..id.LAST LOOP
    UPDATE EMPLOYEES
    SET SALARY = 1.1 * SALARY
    WHERE MANAGER_ID = id(i);
 END LOOP;
END;
/

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 EMPLOYEE_ID, LAST_NAME
    FROM EMPLOYEES
    WHERE MANAGER_ID = 7698;
BEGIN
 -- Efficient method, using bulk bind:
 
 SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT
 INTO empno, ename
 FROM EMPLOYEES
 WHERE MANAGER_ID = 7698;
 
 -- Slower method:
 
 counter := 1;
 
 FOR rec IN c LOOP
    empno(counter) := rec.EMPLOYEE_ID;
    ename(counter) := rec.LAST_NAME;
    counter := counter + 1;
 END LOOP;
END;
/

DECLARE
  TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE;
  empids emp_list := emp_list(182, 187, 193, 200, 204, 206);
  
  TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE;
  bonus_list_inst  bonus_list;
  
BEGIN
  -- Efficient method, using bulk bind:
 
 FORALL i IN empids.FIRST..empids.LAST
 UPDATE EMPLOYEES
 SET SALARY = 0.1 * SALARY
 WHERE EMPLOYEE_ID = empids(i)
 RETURNING SALARY BULK COLLECT INTO bonus_list_inst;
 
 -- Slower method:
 
 FOR i IN empids.FIRST..empids.LAST LOOP
   UPDATE EMPLOYEES
   SET SALARY = 0.1 * SALARY
   WHERE EMPLOYEE_ID = empids(i)
   RETURNING SALARY INTO bonus_list_inst(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 has these examples of cursor variable usage in PL/SQL:

• Example 6-12, "Fetching Data with Cursor Variable"

• Example 6-13, "Cursor Variable with Discriminator"

Example 6-12 creates a package that defines a PL/SQL cursor variable type and two procedures, and then invokes the procedures from a PL/SQL block. The first procedure opens a cursor variable using a bind variable in the WHERE clause. The second procedure uses a cursor variable to fetch rows from the EMPLOYEES table.

CREATE OR REPLACE PACKAGE emp_data AS
  TYPE emp_val_cv_type IS REF CURSOR
  RETURN EMPLOYEES%ROWTYPE;
  
  PROCEDURE open_emp_cv (
    emp_cv       IN OUT emp_val_cv_type,
    dept_number  IN     EMPLOYEES.DEPARTMENT_ID%TYPE
  );
 
 PROCEDURE fetch_emp_data (
   emp_cv   IN  emp_val_cv_type,
   emp_row  OUT EMPLOYEES%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     EMPLOYEES.DEPARTMENT_ID%TYPE
  )
  IS
  BEGIN
    OPEN emp_cv FOR
    SELECT * FROM EMPLOYEES
    WHERE DEPARTMENT_ID = dept_number;
  END open_emp_cv;
  
  PROCEDURE fetch_emp_data (
    emp_cv   IN  emp_val_cv_type,
    emp_row  OUT EMPLOYEES%ROWTYPE
  )
  IS
  BEGIN
    FETCH emp_cv INTO emp_row;
  END fetch_emp_data;
END emp_data;
/
 

DECLARE
  emp_curs     emp_data.emp_val_cv_type;
  dept_number  EMPLOYEES.DEPARTMENT_ID%TYPE;
  emp_row      EMPLOYEES%ROWTYPE;
  
BEGIN
  dept_number := 20;
  
  -- Open cursor, using variable:
 
 emp_data.open_emp_cv(emp_curs, dept_number);
 
 -- Fetch and display data:
 
 LOOP
   emp_data.fetch_emp_data(emp_curs, emp_row);
   EXIT WHEN emp_curs%NOTFOUND;
   DBMS_OUTPUT.PUT(emp_row.LAST_NAME || '  ');
   DBMS_OUTPUT.PUT_LINE(emp_row.SALARY);
 END LOOP;
END;
/

In Example 6-13, the procedure opens a cursor variable for either the EMPLOYEES table or the DEPARTMENTS table, depending on the value of the parameter discrim. The anonymous block invokes the procedure to open the cursor variable for the EMPLOYEES table, but fetches from the DEPARTMENTS table, which raises the predefined exception ROWTYPE_MISMATCH.

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 EMPLOYEES;
    ELSIF discrim = 2 THEN
      OPEN cv FOR
        SELECT * FROM DEPARTMENTS;
    END IF;
  END open_cv;
END emp_dept_data;
/

DECLARE
  emp_rec   EMPLOYEES%ROWTYPE;
  dept_rec  DEPARTMENTS%ROWTYPE;
  cv        Emp_dept_data.CV_TYPE;
BEGIN
  emp_dept_data.open_cv(cv, 1);  -- Open cv for EMPLOYEES fetch.
  FETCH cv INTO dept_rec;        -- Fetch from DEPARTMENTS.
  DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID);
  DBMS_OUTPUT.PUT_LINE('  ' || dept_rec.LOCATION_ID);
EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
     BEGIN
       DBMS_OUTPUT.PUT_LINE
         ('Row type mismatch, fetching EMPLOYEES data ...');
       FETCH cv INTO emp_rec;
       DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID);
       DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.LAST_NAME);
     END;
END;
/

Row type mismatch, fetching EMPLOYEES data ...
90  King

Declaring Exceptions and Exception Handlers

User-defined exceptions are explicitly defined and raised within the PL/SQL block, to process errors specific to the application. When an exception is raised, the usual execution of the PL/SQL block stops, and 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 6-1 shows:

• 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 6-1 Exceptions and User-Defined Errors

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

Unhandled Exceptions

In database PL/SQL 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 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 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 from a constraint violation, then Oracle Database returns ORA-02055. Subsequent statements, or subprogram invocations, return 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, these 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

• 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:

EXCEPTION
    WHEN ZERO_DIVIDE THEN
    /* Handle the exception */

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. For 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 7, "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 8, "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 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 subprogram involved in making the connect call.

When a debugger becomes connected to a session, the session login user and the 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 run 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 PL/SQL compilation parameter PLSQL_DEBUG 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 units. Several of the debuggers available, such as Oracle Procedure Builder and various third-party vendor solutions, use this API.

Privileges Required to Invoke a Subprogram

You do not need privileges to invoke:

• Standalone subprograms that you own

• Subprograms in packages that you own

• Public standalone subprograms

• Subprograms in public packages

To invoke a standalone or packaged subprogram owned by another user:

• You must have the EXECUTE privilege for the standalone subprogram or for the package containing the subprogram, or you must have the EXECUTE ANY PROCEDURE system privilege.

• If you are running 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 jdoe.Fire_emp (1043);
  EXECUTE jdoe.Hire_fire.Fire_emp (1043);
  

• If the subprogram is a definer's-rights (DR) subprogram, then it runs with the privileges of the 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. You must have all the necessary object privileges for any referenced objects; that is, all objects accessed by the subprogram through external references that are resolved in your schema. You can hold these privileges either directly or through a role. Roles are enabled unless an IR subprogram is invoked directly or indirectly by a DR subprogram.

Invoking a Subprogram Interactively from Oracle Tools

You can invoke a subprogram interactively from an Oracle Database tool, such as SQL*Plus. Example 6-15 uses SQL*Plus to create a procedure and then invokes it in two different ways.

CREATE OR REPLACE PROCEDURE salary_raise (
  employee  EMPLOYEES.EMPLOYEE_ID%TYPE,
  increase  EMPLOYEES.SALARY%TYPE
)
IS
BEGIN
  UPDATE EMPLOYEES
  SET SALARY = SALARY + increase
  WHERE EMPLOYEE_ID = employee;
END;
/
 

BEGIN
  salary_raise(205, 200);
END;
/
 

PL/SQL procedure successfully completed.
 

EXECUTE salary_raise(205, 200);
 

PL/SQL procedure successfully completed.

Some interactive tools allow you to create session variables, which you can use for the duration of the session. Using SQL*Plus, Example 6-16 creates, uses, and prints a session variable.

-- Create function for later use:

CREATE OR REPLACE FUNCTION get_job_id (
  emp_id  EMPLOYEES.EMPLOYEE_ID%TYPE
) RETURN EMPLOYEES.JOB_ID%TYPE
IS
  job_id  EMPLOYEES.JOB_ID%TYPE;
BEGIN
  SELECT JOB_ID INTO job_id
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
 
  RETURN job_id;
END;
/
-- Create session variable:
 
VARIABLE job VARCHAR2(10);
 
-- Run function and store returned value in session variable:
 
EXECUTE :job := get_job_id(204);
 
PL/SQL procedure successfully completed.
 

PRINT job;
 

JOB
--------------------------------
PR_REP

Invoking a Subprogram from Another Subprogram

A subprogram or a trigger can invoke another stored subprogram. In Example 6-17, the procedure print_mgr_name invokes the procedure print_emp_name.

Recursive subprogram invocations are allowed (that is, a subprogram can invoke itself).

-- Create procedure that takes employee's ID and prints employee's name:
 
CREATE OR REPLACE PROCEDURE print_emp_name (
  emp_id  EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
  fname  EMPLOYEES.FIRST_NAME%TYPE;
  lname  EMPLOYEES.LAST_NAME%TYPE;
BEGIN
  SELECT FIRST_NAME, LAST_NAME
  INTO fname, lname
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
 
  DBMS_OUTPUT.PUT_LINE (
    'Employee #' || emp_id || ':  ' || fname || ' ' || lname
  );
END;
/
 
-- Create procedure that takes employee's ID and prints manager's name:
 
CREATE OR REPLACE PROCEDURE print_mgr_name (
  emp_id  EMPLOYEES.EMPLOYEE_ID%TYPE
)
IS
  mgr_id  EMPLOYEES.MANAGER_ID%TYPE;
BEGIN
  SELECT MANAGER_ID
  INTO mgr_id
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;
 
 DBMS_OUTPUT.PUT_LINE (
   'Manager of employee #' || emp_id || ' is:  '
 );
 
 print_emp_name(mgr_id);
END;
/
 

BEGIN
  print_emp_name(200);
  print_mgr_name(200);
END;
/
 

Employee #200:  Jennifer Whalen
Manager of employee #200 is:
Employee #101:  Neena Kochhar

Invoking a Subprogram from a 3GL Application

A 3GL database application, such as a precompiler or an OCI application, can invoke a subprogram from within its own code.

Assume that the procedure Fire_emp1 was created as follows:

CREATE OR REPLACE PROCEDURE fire_emp1 (Emp_id NUMBER) AS
  BEGIN
    DELETE FROM Emp_tab WHERE Empno = Emp_id;
  END;

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

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

Synonyms for Remote Subprograms

You can create a synonym for a remote subprogram name and database link, and then use the synonym to invoke the subprogram. For example:

CREATE SYNONYM synonym1 for fire_emp1@boston_server;

EXECUTE synonym1(1043);
/

The synonym 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.

Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the object and regardless of which database holds the object. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

Because subprograms defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual subprograms within a package.

If you do not want to use a synonym, you can create a local subprogram to invoke the remote subprogram. For example:

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

Committing Transactions

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 run 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 transaction modifies data on two or more databases. A distributed transaction 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.

Why Invoke Stored PL/SQL Subprograms from SQL Statements?

Invoking PL/SQL subprograms in SQL statements can:

• 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 data types (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:

• 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 instead of:

• Column name in the SELECT list

• Table name in the FROM clause

A PL/SQL function cannot appear in these 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 these 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 return value (if any) must have Oracle built-in data types (such as CHAR, DATE, or NUMBER), not PL/SQL data types (such as BOOLEAN, RECORD, or TABLE).

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

The function in Example 6-18 satisfies the preceding requirements.

DROP TABLE payroll;  -- in case it exists
CREATE TABLE payroll (
  srate  NUMBER,
  orate  NUMBER,
  acctno NUMBER
);
 
CREATE OR REPLACE FUNCTION gross_pay (
  emp_id  IN NUMBER,
  st_hrs  IN NUMBER := 40,
  ot_hrs  IN NUMBER := 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 Example 6-19, the SQL statement CALL invokes the PL/SQL function f1, whose formal parameter and return value have PL/SQL data type PLS_INTEGER. The CALL statement succeeds because the actual parameter, 2, is implicitly converted to the data type 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;
 

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 before 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 OR REPLACE FUNCTION f1 (
  p1 NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN p1 * 2;
END;
/

CREATE OR REPLACE FUNCTION f1 (
  p1 NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
  RETURN p1 * 2;
END;
/

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

DROP TABLE accounts; -- in case it exists
CREATE TABLE accounts (
  acctno   INTEGER,
  balance  NUMBER
);
 
INSERT INTO accounts (acctno, balance)
VALUES (12345, 1000.00);
 
CREATE OR REPLACE PACKAGE finance AS
  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
  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;
BEGIN
  SELECT finance.compound_(5, 1000, 6)
  INTO interest
  FROM accounts
  WHERE acctno = 12345;
END;
/

CREATE OR REPLACE PACKAGE p IS
  PROCEDURE java_sleep (milli_seconds IN NUMBER)
  AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
  PRAGMA RESTRICT_REFERENCES(java_sleep,WNDS,TRUST);
  
  FUNCTION f (n NUMBER) RETURN NUMBER;
END p;
/
CREATE OR REPLACE PACKAGE BODY p IS
  FUNCTION f (
    n NUMBER
   ) RETURN NUMBER
   IS
   BEGIN
     java_sleep(n);
     RETURN n;
   END f;
END p;
/

CREATE OR REPLACE PACKAGE p IS
  PROCEDURE java_sleep (milli_seconds IN NUMBER)
  AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
  
  FUNCTION f (n NUMBER) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(f,WNDS,TRUST);
END p;
/
CREATE OR REPLACE PACKAGE BODY p IS
  FUNCTION f (
    n NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    java_sleep(n);
     RETURN n;
  END f;
END p;
/

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

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

CREATE OR REPLACE PACKAGE tests AS
  FUNCTION valid (x NUMBER) RETURN CHAR;
  FUNCTION valid (x DATE) RETURN CHAR;
  PRAGMA RESTRICT_REFERENCES (valid, WNDS);
END;
/