11 Coding PL/SQL Subprograms and Packages

11.3.1 PLSQL_OPTIMIZE_LEVEL Compilation Parameter

The PL/SQL optimize level determines how much the PL/SQL optimizer can rearrange code for better performance. This level is set with the compilation parameter PLSQL_OPTIMIZE_LEVEL (whose default value is 2).

To change the PL/SQL optimize level for your session, use the SQL command ALTER SESSION. Changing the level for your session affects only subsequently created PL/SQL units. To change the level for an existing PL/SQL unit, use an ALTER command with the COMPILE clause.

To display the current value of PLSQL_OPTIMIZE_LEVEL for one or more PL/SQL units, use the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS.

Example 11-1 creates two procedures, displays their optimize levels, changes the optimize level for the session, creates a third procedure, and displays the optimize levels of all three procedures. Only the third procedure has the new optimize level. Then the example changes the optimize level for only one procedure and displays the optimize levels of all three procedures again.

CREATE OR REPLACE PROCEDURE p1 AUTHID DEFINER AS
BEGIN
  NULL;
END;
/
CREATE OR REPLACE PROCEDURE p2 AUTHID DEFINER AS
BEGIN
  NULL;
END;
/

SELECT NAME, PLSQL_OPTIMIZE_LEVEL
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE'
ORDER BY NAME;
 

NAME                           PLSQL_OPTIMIZE_LEVEL
------------------------------ --------------------
P1                                                2
P2                                                2
 
2 rows selected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=1;
 
CREATE OR REPLACE PROCEDURE p3 AUTHID DEFINER AS
BEGIN
  NULL;
END;
/
 

SELECT NAME, PLSQL_OPTIMIZE_LEVEL
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE'
ORDER BY NAME;
 

NAME                           PLSQL_OPTIMIZE_LEVEL
------------------------------ --------------------
P1                                                2
P2                                                2
P3                                                1
 
3 rows selected.

ALTER PROCEDURE p1 COMPILE PLSQL_OPTIMIZE_LEVEL=3;
 

SELECT NAME, PLSQL_OPTIMIZE_LEVEL
FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE'
ORDER BY NAME;

NAME                           PLSQL_OPTIMIZE_LEVEL
------------------------------ --------------------
P1                                                3
P2                                                2
P3                                                1
 
3 rows selected.

11.4.1 Privileges Needed to Create Subprograms and Packages

To create a standalone subprogram or package in your own schema, you must have the CREATE PROCEDURE system privilege. To create a standalone subprogram or package in another schema, you must have the CREATE ANY PROCEDURE system privilege.

If the subprogram or package that you create references schema objects, then you must have the necessary object privileges for those objects. These privileges must be granted to you explicitly, not through roles.

If the privileges of the owner of a subprogram or package change, then the subprogram or package must be reauthenticated before it is run. If a necessary object privilege for a referenced object is revoked from the owner of the subprogram or package, then the subprogram cannot run.

Granting the EXECUTE privilege on a subprogram lets users run that subprogram under the security domain of the subprogram owner, so that the user need not be granted privileges to the objects that the subprogram references. The EXECUTE privilege allows more disciplined and efficient security strategies for database applications and their users. Furthermore, it allows subprograms and packages to be stored in the data dictionary (in the SYSTEM tablespace), where no quota controls the amount of space available to a user who creates subprograms and packages.

11.4.2 Creating Subprograms and Packages

This topic explains how to create standalone subprograms and packages, using SQL Data Definition Language (DDL) statements.

The DDL statements for creating standalone subprograms and packages are:

• CREATE FUNCTION, described in Oracle Database PL/SQL Language Reference

• CREATE PROCEDURE, described in Oracle Database PL/SQL Language Reference

• CREATE PACKAGE, described in Oracle Database PL/SQL Language Reference

• CREATE PACKAGE BODY, described in Oracle Database PL/SQL Language Reference

  The name of a package and the names of its public objects must be unique within the package schema. The package specification and body must have the same name. Package constructs must have unique names within the scope of the package, except for overloaded subprograms.

Each of the preceding CREATE statements has an optional OR REPLACE clause. Specify OR REPLACE to re-create an existing PL/SQL unit—that is, to change its declaration or definition without dropping it, re-creating it, and regranting object privileges previously granted on it. If you redefine a PL/SQL unit, the database recompiles it.

Using any text editor, create a text file that contains DDL statements for creating any number of subprograms and packages.

To run the DDL statements, use an interactive tool such as SQL*Plus. The SQL*Plus command START or @ runs a script. For example, this SQL*Plus command runs the script my_app.sql:

@my_app

(For information about runnings scripts in SQL*Plus, see SQL*Plus User's Guide and Reference.)

Alternatively, you can create and run the DDL statements using SQL Developer. For information about SQL Developer, see Oracle SQL Developer User's Guide.

11.4.3 PL/SQL Object Size Limits

The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.

The most closely related number that a user can access is PARSED_SIZE, a column in the static data dictionary view *_OBJECT_SIZE. The column PARSED_SIZE gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.

11.4.4 PL/SQL Data Types

This topic introduces the PL/SQL data types and refers to other chapters or documents for more information.

Use the correct and most specific PL/SQL data type for each PL/SQL variable in your database application. For the reasons, see Section 7.1, "Using the Correct and Most Specific Data Type."

Topics:

• PL/SQL Scalar Data Types

• PL/SQL Composite Data Types

• Abstract Data Types

11.4.5 Returning Result Sets to Clients

In PL/SQL, as in traditional database programming, you use cursors to process query result sets. A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

A cursor that is constructed and managed by PL/SQL is an implicit cursor. A cursor that you construct and manage is an explicit cursor. The only advantage of an explicit cursor over an implicit cursor is that with an explicit cursor, you can limit the number of fetched rows (for details, see Oracle Database PL/SQL Language Reference). For general information about cursors, see Oracle Database PL/SQL Language Reference. For information about query set result processing with implicit and explicit cursors, see Oracle Database PL/SQL Language Reference.

A cursor variable is a pointer to a cursor. That is, its value is the address of a cursor, not the cursor itself. Therefore, a cursor variable has more flexibility than an explicit cursor. However, a cursor variable also has costs that an explicit cursor does not.

Topics:

• Advantages of Cursor Variables

• Disadvantages of Cursor Variables

• Returning Query Results Implicitly

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
  CURSOR e_c IS SELECT * FROM DUAL d1;  -- explicit cursor
  c_v SYS_REFCURSOR;                    -- cursor variable
  rec DUAL%ROWTYPE;
BEGIN
  OPEN e_c;                             -- explicit cursor
  FETCH e_c INTO rec;
  CLOSE e_c;

  OPEN c_v FOR SELECT * FROM DUAL d2;   -- cursor variable
  FETCH c_v INTO rec;
  CLOSE c_v;
END;
/
BEGIN
  FOR i IN 1..10 LOOP                   -- execute p 10 times
     p;
  END LOOP;

SELECT * FROM DUAL D1;
 
call     count
------- ------
Parse        1
Execute     10
Fetch       10
------- ------
total       21
****************
SELECT * FROM DUAL D2;
 
 
call     count
------- ------
Parse       10
Execute     10
Fetch       10
------- ------
total       30

CREATE TABLE t AS
  SELECT * FROM ALL_OBJECTS;
 
SELECT COUNT(*) FROM t;
 

  COUNT(*)
----------
     70788

DECLARE
  c_v SYS_REFCURSOR;
  rec t%ROWTYPE;
BEGIN
  FOR x IN (SELECT * FROM t exp_cur) LOOP  -- implicit cursor
    NULL;
  END LOOP;
 
  OPEN c_v FOR SELECT * FROM t cur_var;    -- cursor variable
 
  LOOP
    FETCH c_v INTO rec;
    EXIT WHEN c_v%NOTFOUND;
  END LOOP;
 
  CLOSE c_v;
END;
/

SELECT * FROM T EXP_CUR

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      722      0.23       0.23          0       1748          0       72198
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      724      0.23       0.23          0       1748          0       72198
********************************************************************************
SELECT * FROM T CUR_VAR

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    72199      0.40       0.42          0      72203          0       72198
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    72201      0.40       0.42          0      72203          0       72198

11.4.6 Returning Large Amounts of Data from a Function

In a data warehousing environment, you might use PL/SQL functions to transform large amounts of data. You might pass the data through a series of transformations, each performed by a different function. PL/SQL table functions let you perform such transformations without significant memory overhead or the need to store the data in tables between each transformation stage. These functions can accept and return multiple rows, can return rows as they are ready rather than all at once, and can be parallelized.

11.4.7 PL/SQL Function Result Cache

Using the PL/SQL function result cache can save significant space and time. Each time a result-cached PL/SQL function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed. Because the cache is stored in a shared global area (SGA), it is available to any session that runs your application.

If a database object that was used to compute a cached result is updated, the cached result becomes invalid and must be recomputed.

The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.

For more information about the PL/SQL function result cache, see Oracle Database PL/SQL Language Reference.

11.4.8 Overview of Bulk Binding

Oracle Database uses two engines to run PL/SQL units. The PL/SQL engine runs the procedural statements and the SQL engine runs the SQL statements. Every SQL statement causes a context switch between the two engines. You can greatly improve the performance of your database application by minimizing the number of context switches for each PL/SQL unit.

When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required can cause poor performance. Collections include:

• Associative arrays

• Variable-size arrays

• Nested tables

• Host arrays

Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection between the two engines in a single operation.

Typically, bulk binding improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binding. Consider using bulk binding to improve the performance of DML and SELECT INTO statements that reference collections and FOR loops that reference collections and return DML.

Topics:

• DML Statements that Reference Collections

• SELECT Statements that Reference Collections

• FOR Loops that Reference Collections and Return DML

DECLARE
  TYPE numlist IS VARRAY (100) OF NUMBER;
  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;
/

11.4.9 PL/SQL Dynamic SQL

Dynamic SQL is a programming methodology for generating and running SQL statements at runtime. It is useful when writing general-purpose and flexible programs like dynamic query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compile time the full text of a SQL statement or the number or data types of its input and output variables.

If you do not need dynamic SQL, then use static SQL, which has these advantages:

• Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.

• Successful compilation creates schema object dependencies.

  For information about schema object dependency, see Chapter 23, "Understanding Schema Object Dependency."

For information about dynamic SQL, see Oracle Database PL/SQL Language Reference.

For information about static SQL, see Oracle Database PL/SQL Language Reference.

11.8.1 Privileges Required to Invoke a Stored 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 stored subprogram owned by another user, you must have the EXECUTE privilege for the standalone subprogram or for the package containing the package subprogram, or you must have the EXECUTE ANY PROCEDURE system privilege. If the subprogram is remote, then you must be granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not through a role.

11.8.2 Invoking a Subprogram Interactively from Oracle Tools

You can invoke a subprogram interactively from an Oracle Database tool, such as SQL*Plus. Example 11-7 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 11-8 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

11.8.3 Invoking a Subprogram from Another Subprogram

A subprogram or a trigger can invoke another stored subprogram. In Example 11-9, 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

11.8.4 Invoking a Remote Subprogram

A remote subprogram is stored on a different database from its invoker. A remote subprogram invocation must include the subprogram name, a database link to the database on which the subprogram is stored, and an actual parameter for every formal parameter (even if the formal parameter has a default value).

For example, this SQL*Plus statement invokes the stored standalone procedure fire_emp1, which is referenced by the local database link named boston_server:

EXECUTE fire_emp1@boston_server(1043);

Topics:

• Synonyms for Remote Subprograms

• Transactions That Invoke Remote Subprograms

CREATE SYNONYM synonym1 for fire_emp1@boston_server;
EXECUTE synonym1(1043);

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

11.9.1 Why Invoke PL/SQL Functions from SQL Statements?

Invoking PL/SQL functions 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 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 run in parallel (using the parallel query option).

11.9.2 Where PL/SQL Functions Can Appear in SQL Statements

A PL/SQL function can appear in a SQL statement wherever a 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

11.9.3 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 either a user-defined aggregate function or a row function.

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

The function in Example 11-10 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;
/

11.9.4 Controlling Side Effects of PL/SQL Functions Invoked from SQL Statements

A subprogram has side effects if it changes anything except the values of its own local variables. For example, a subprogram that changes any of the following has side effects:

• Its own OUT or IN OUT parameter

• A global variable

• A public variable in a package

• A database table

• The database

• The external state (by invoking DBMS_OUTPUT or sending e-mail, for example)

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.

Some side effects are not allowed in a function invoked from a SQL query or DML statement.

Before Oracle Database 8g Release 1 (8.1), application developers used PRAGMA RESTRICT_REFERENCES to assert the purity (freedom from side effects) of a function. This pragma remains available for backward compatibility, but do not use it in new applications. Instead, specify the optimizer hints DETERMINISTIC and PARALLEL_ENABLE when you create the function. For information about DETERMINISTIC and PARALLEL_ENABLE, see Oracle Database PL/SQL Language Reference.

Topics:

• Restrictions on Functions Invoked from SQL Statements

• PL/SQL Functions Invoked from Parallelized SQL Statements

• PRAGMA RESTRICT_REFERENCES (deprecated)

PRAGMA RESTRICT_REFERENCES (function_name, assertion [, assertion]... );

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

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

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

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

11.10.1 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 lets you develop 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 12, "Using PL/Scope."

11.10.2 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 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 13, "Using the PL/SQL Hierarchical Profiler."

11.10.3 Compiling Code for Debugging

A debugger can stop on individual code lines and access variables only in code compiled with debug information generated.

To compile a PL/SQL unit with debug information generated, set the compilation parameter PLSQL_OPTIMIZE_LEVEL to 1 (the default value is 2). For more information about PLSQL_OPTIMIZE_LEVEL, see Oracle Database Reference. For information about PL/SQL units, see Section 11.3.

11.10.4 Privileges for Debugging PL/SQL and Java Stored Subprograms

As of Oracle Database 10g, a new privilege model applies to debugging PL/SQL and Java code running in the database. This model applies to all development environments.

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. The effective user might be the owner of a DR subprogram involved in making the connect call.

When a session connects to a debugger, the session login user and the enabled session-level roles are fixed as the privilege environment for that debugging connection. The privileges needed for debugging must be granted to that combination of user and roles on the relevant code. The privileges are:

• To display and change variables declared in a PL/SQL package specification or Java public variables: either EXECUTE or DEBUG.

• To display and change private variables, or to breakpoint and run code lines step by step: DEBUG

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

11.10.5 DBMS_DEBUG Package

The DBMS_DEBUG package is a PL/SQL interface to the PL/SQL debugger layer, Probe, in the Oracle server. This API is primarily intended to implement server-side debuggers and it provides a way to debug server-side PL/SQL program units.

Several debuggers, such as Oracle Procedure Builder and various third-party vendor solutions, use this API.

If you are writing low-level debugging code (for example, code for part of a debugger), you might need to use DBMS_DEBUG.