10
Using Procedures and Packages

This chapter discusses some of the procedural capabilities of Oracle for application development, including:

• PL/SQL Program Units

• Wrapping PL/SQL Code

• Remote Dependencies

• Cursor Variables

• Compile-Time Errors

• Run-Time Error Handling

• Debugging

• Calling Stored Procedures

• Calling Remote Procedures

• Calling Stored Functions from SQL Expressions

PL/SQL Program Units

PL/SQL is a modern, block-structured programming language. It provides a number of features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.

You can directly enter SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use procedures, supplied by Oracle, to perform data definition language (DDL) statements.

PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.

You can even use PL/SQL for some database applications in place of 3GL programs that use embedded SQL or the Oracle Call Interface (OCI).

PL/SQL program units include:

• Anonymous Blocks

• Stored Program Units (Procedures, Functions, and Packages)

• Triggers

  See Also: For complete information about the PL/SQL language, see the PL/SQL User's Guide and Reference.

Anonymous Blocks

An anonymous block is a PL/SQL program unit that has no name, and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. 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 called 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 short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:

DECLARE
   Emp_name    VARCHAR2(10);
   Cursor      c1 IS SELECT Ename FROM Emp_tab
                  WHERE Deptno = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
END;

Exceptions let you handle Oracle error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to abend. The following anonymous block handles the predefined Oracle exception NO_DATA_FOUND (which would result 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 call stored procedures or to open cursor variables.

Stored Program Units (Procedures, Functions, and Packages)

A stored procedure, function, or package is a PL/SQL program unit that has the following features:

• Has a name.

• Can take parameters, and can return values.

• Is stored in the data dictionary.

• Can be called by many users.

  Note: The term stored procedure is sometimes used generically to cover both stored procedures and stored functions. The only difference between procedures and functions is that functions always return a single value to the caller, while procedures do not return a value to the caller.

Naming Procedures and Functions

Because a procedure or function is stored in the database, it must be named. This distinguishes it from other stored procedures and makes it possible for applications to call it. Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier.

Parameters for Procedures and Functions

Stored procedures and functions can take parameters. The following example shows a stored procedure that is similar to the anonymous block in "Anonymous Blocks".

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;

In this stored procedure example, the department number is an input parameter which is used when the parameterized cursor c1 is opened.

The formal parameters of a procedure have three major parts:

Name	This must be a legal PL/SQL identifier.
Mode	This indicates whether the parameter is an input-only parameter (IN), an output-only parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, then IN is assumed.
Datatype	This is a standard PL/SQL datatype.

Parameter Modes

Parameter modes define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take no arguments and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Table 10-1 summarizes the information about parameter modes.

Table 10-1 Parameter Modes

Parameter Datatypes

The datatype of a formal parameter consists of one of the following:

• An unconstrained type name, such as NUMBER or VARCHAR2.

• A type that is constrained using the %TYPE or %ROWTYPE attributes.

  Note: Numerically constrained types such as NUMBER(2) or VARCHAR2(20) are not allowed in a parameter list.

%TYPE and %ROWTYPE Attributes

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)

This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

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

Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:

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;

You could call this procedure from a PL/SQL block as follows:

DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- call 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;

Stored functions can also return values that are declared using %ROWTYPE. For example:

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

Tables and Records

You can pass PL/SQL tables as parameters to stored procedures and functions. You can also pass tables of records as parameters.

Default Parameter Values

Parameters can take default values. Use the DEFAULT keyword or the assignment operator to give a parameter a default value. For example, the specification for the Get_emp_names procedure could be written as the following:

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

or

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

When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. When you do specify the parameter value on the call, it overrides the default value.

Creating Stored Procedures and Functions

Use your usual text editor to write the procedure or function. At the beginning of the procedure, place the following statement:

CREATE PROCEDURE Procedure_name AS   ...

For example, to use the example in "%TYPE and %ROWTYPE Attributes", create a text (source) file called get_emp.sql containing the following code:

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

Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering the following statement:

SQLPLUS> @get_emp

This loads the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.

Use the CREATE [OR REPLACE] FUNCTION... statement to store functions.

You can use either the keyword IS or AS after the procedure parameter list.

Privileges to Create Procedures and Functions

To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

  Note: To create without errors (to compile the procedure or package successfully) requires the following additional privileges: The owner of the procedure or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code. The owner cannot obtain required privileges through roles.

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

The EXECUTE privilege on a procedure gives a user the right to run a procedure owned by another user. Privileged users run the procedure under the security domain of the procedure's owner. Therefore, users never need to be granted the privileges to the objects referenced by a procedure. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all procedures and packages are stored in the data dictionary (in the SYSTEM tablespace). No quota controls the amount of space available to a user who creates procedures and packages.

Altering Stored Procedures and Functions

To alter a stored procedure or function, you must first drop it using the DROP PROCEDURE or DROP FUNCTION statement, then recreate it using the CREATE PROCEDURE or CREATE FUNCTION statement. Alternatively, use the CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION statement, which first drops the procedure or function if it exists, then recreates it as specified.

Dropping Procedures and Functions

A stand-alone procedure, a stand-alone function, a package body, or an entire package can be dropped using the SQL statements DROP PROCEDURE, DROP FUNCTION, DROP PACKAGE BODY, and DROP PACKAGE, respectively. A DROP PACKAGE statement drops both a package's specification and body.

The following statement drops the Old_sal_raise procedure in your schema:

DROP PROCEDURE Old_sal_raise;

Privileges to Drop Procedures and Functions

To drop a procedure, function, or package, the procedure or package must be in your schema, or you must have the DROP ANY PROCEDURE privilege. An individual procedure within a package cannot be dropped; the containing package specification and body must be re-created without the procedures to be dropped.

External Procedures

A PL/SQL procedure executing on an Oracle Server can call an external procedure written in a 3GL. The 3GL procedure runs in a separate address space from that of the Oracle Server.

PL/SQL Packages

A package is an encapsulated collection of related program objects (e.g., procedures, functions, variables, constants, cursors, and exceptions) stored together in the database.

Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over stand-alone procedures and functions. For example, they:

• Let you organize your application development more efficiently.

• Let you grant privileges more efficiently.

• Let you modify package objects without recompiling dependent schema objects.

• Enable Oracle to read multiple package objects into memory at once.

• Can contain global variables and cursors that are available to all procedures and functions in the package.

• Let you overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

  See Also: The PL/SQL User's Guide and Reference has more information about subprogram name overloading.

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example

The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:

CREATE PACKAGE BODY Employee_management AS
   FUNCTION Hire_emp (Name 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 call to this function.

   BEGIN
      SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual;
      INSERT INTO Emp_tab VALUES (New_empno, Name, 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_tab 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_tab
         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;

Creating Packages

Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public procedures and functions declared in the package specification.

You can also define private, or local, package procedures, functions, and variables in a package body. These objects can only be accessed by other procedures and functions in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements would then be the following:

CREATE OR REPLACE PACKAGE Package_name AS ...

and

CREATE OR REPLACE PACKAGE BODY Package_name AS ...

Creating Packaged Objects

The body of a package can contain include:

• Procedures and functions declared in the package specification.

• Definitions of cursors declared in the package specification.

• Local procedures and functions, not declared in the package specification.

• Local variables.

Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, or used, by external users that have EXECUTE permission for the package or that have EXECUTE ANY PROCEDURE privileges.

When you create the package body, make sure that each procedure that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.

Privileges to Create or Drop Packages

The privileges required to create or drop a package specification or package body are the same as those required to create or drop a stand-alone procedure or function.

Naming Packages and Package Objects

The names of a package and all public objects in the package must be unique within a given schema. The package specification and its body must have the same name. All package constructs must have unique names within the scope of the package, unless overloading of procedure names is desired.

Package Invalidations and Session State

Each session that references a package object has its own instance of the corresponding package, including persistent state for any public and private variables, cursors, and constants. If any of the session's instantiated packages (specification or body) are subsequently invalidated and recompiled, then all other dependent package instantiations (including state) for the session are lost.

For example, assume that session S instantiates packages P1 and P2, and that a procedure in package P1 calls a procedure in package P2. If P1 is invalidated and recompiled (for example, as the result of a DDL operation), then the session S instantiations of both P1 and P2 are lost. In such situations, a session receives the following error the first time it attempts to use any object of an invalidated package instantiation:

ORA-04068: existing state of packages has been discarded

The second time a session makes such a package call, the package is reinstantiated for the session without error.

In most production environments, DDL operations that can cause invalidations are usually performed during inactive working hours; therefore, this situation might not be a problem for end-user applications. However, if package specification or body invalidations are common in your system during working hours, then you might want to code your applications to detect for this error when package calls are made.

Oracle Supplied Packages

There are many built-in packages provided with the Oracle Server, either to extend the functionality of the database or to give PL/SQL access to SQL features. You may take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages for ideas in creating your own stored procedures.

This section lists each of the supplied packages and indicates where they are described in more detail. These packages run as the calling user, rather than the package owner. Unless otherwise noted, the packages are callable through public synonyms of the same name.

Table 10-2 List of Oracle Supplied Packages

Calendar 

DBMS_ALERT

DBMS_APPLICATION_INFO

DBMS_AQ

DBMS_AQADM

DBMS_DDL

DBMS_DEBUG

DBMS_DEFER 

DBMS_DEFER_QUERY

DMBS_DEFER_SYS

DBMS_DESCRIBE

DBMS_DISTRIBUTED_TRUST_ADMIN

DBMS_HS

DBMS_HS_PASSTHROUGH

DBMS_IOT

DBMS_JOB

DBMS_LOB

DBMS_LOCK

DBMS_LOGMNR

DBMS_LOGMNR_D

DBMS_OFFLINE_OG

DBMS_OFFLINE_SNAPSHOT

DBMS_OLAP

DBMS_ORACLE_TRACE_AGENT 

DBMS_ORACLE_TRACE_USER 

DBMS_OUTPUT

DBMS_PCLXUTIL

DBMS_PIPE

DBMS_PROFILER

DBMS_RANDOM 

DBMS_RECTIFIER_DIFF

DBMS_REFRESH

DBMS_REPAIR

DBMS_REPCAT

DBMS_REPCAT_ADMIN

DBMS_REPCAT_INSTATIATE

DBMS_REPCAT_RGT

DBMS_REPUTIL

DBMS_RESOURCE_MANAGER

DBMS_RESOURCE_MANAGER_PRIVS

DBMS_RLS

DBMS_ROWID

DBMS_SESSION

DBMS_SHARED_POOL

DBMS_SNAPSHOT

DBMS_SPACE

DBMS_SPACE_ADMIN

DBMS_SQL

DBMS_STANDARD 

DBMS_STATS

DBMS_TRACE

DBMS_TRANSACTION

DBMS_TTS

DBMS_UTILITY

DEBUG_EXTPROC

OUTLN_PKG

PLITBLM 

SDO_ADMIN

SDO_GEOM

SDO_MIGRATE

SDO_TUNE

STANDARD

TimeSeries 

TimeScale

TSTools

UTL_COLL 

UTL_FILE

UTL_HTTP

UTL_PG

UTL_RAW

UTL_REF

Vir_Pkg 

Bulk Binds

Oracle uses two engines to run PL/SQL blocks and subprograms: the PL/SQL engine and the SQL engine. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, which results in a performance penalty.

Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include the following:

• Varrays

• Nested tables

• Index-by 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. Without bulk binds, the elements in a collection are sent to the SQL engine individually, whereas bulk binds pass the entire collection back and forth between the two engines.

Using bulk binds, you can improve performance by reducing the number of context switches required to run SQL statements that use collection elements. Typically, using bulk binds 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 will be with bulk binds.

When to Use Bulk Binds

The following sections discuss common scenarios where bulk binds can improve performance. If you have, or plan to have, similar scenarios in your applications, then you should consider using bulk binds.

DML Statements Referencing Collections

Bulk binds can be used to improve the performance of DML statements that reference collections. To bulk-bind an input collection before sending it to the SQL engine, use the FORALL keyword. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements.

For example, the following PL/SQL block increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, without using bulk binds:

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);
   END LOOP;
END;

To run this block, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated. If there are many employees to update, then the large number of context switches between the PL/SQL engine and the SQL engine can hurt performance.

Use the FORALL keyword to bulk-bind the collection and improve performance:

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FORALL i IN Id.FIRST..Id.LAST   -- bulk-bind the VARRAY
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);
END;

SELECT Statements Referencing Collections

Bulk binds can be used to improve the performance of SELECT statements that reference collections. To bulk-bind output collections before returning them to the PL/SQL engine, use the keywords BULK COLLECT INTO.

For example, the following PL/SQL block returns the employee name and job for employees whose manager's ID number is 7698, without using bulk binds:

DECLARE
   TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   Empno VAR_TAB;
   Ename VAR_TAB;
   Counter NUMBER;
   CURSOR C IS
      SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;
BEGIN

-- Initialize variable tracing number of employees returned.

   counter := 1;  

-- Find all employees whose manager's ID number is 7698.

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

PL/SQL sends a SQL statement to the SQL engine for each employee that is selected. If there are many employees selected, then the large number of context switches between the PL/SQL engine and the SQL engine can hurt performance.

Use the BULK COLLECT INTO keywords to bulk-bind the collection and improve performance:

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

FOR Loops that Reference Collections and the Returning Into Clause

Bulk binds can be used to improve the performance of FOR loops that reference collections and return DML. If you have, or plan to have, PL/SQL code that does this, then you can use the FORALL keyword along with the BULK COLLECT INTO keywords to improve performance.

For example, the following PL/SQL block updates the Emp_tab table by computing bonuses for a collection of employees; then it returns the bonuses in a column called Bonlist. Both actions are performed without using bulk binds:

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

PL/SQL sends a SQL statement to the SQL engine for each employee that is updated. If there are many employees updated, then the large number of context switches between the PL/SQL engine and the SQL engine can hurt performance.

Use the FORALL and BULK COLLECT INTO keywords together to bulk-bind the collection and improve performance:

DECLARE
   TYPE Emplist IS VARRAY(100) OF NUMBER;
   TYPE Numlist IS TABLE OF Emp_tab.Sal%TYPE;
   Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698);
   Bonlist NUMLIST;
BEGIN
   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;
END;

Triggers

A trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. You can also define INSTEAD OF triggers or system triggers (triggers on DATABASE and SCHEMA).

Wrapping PL/SQL Code

You can deliver your stored procedures in object code format using the PL/SQL Wrapper. Wrapping your PL/SQL code hides your application internals. To run the PL/SQL Wrapper, enter the WRAP statement at your system prompt using the following syntax:

wrap INAME=input_file [ONAME=ouput_file]

Remote Dependencies

Dependencies among PL/SQL program units can be handled in two ways:

• Timestamps

• Signatures

Timestamps

If timestamps are used to handle dependencies among PL/SQL program units, then whenever you alter a program unit or a relevant schema object, all of its dependent units are marked as invalid and must be recompiled before they can be run.

Each program unit carries a timestamp that is set by the server when the unit is created or recompiled. Figure 10-1 demonstrates this graphically. Procedures P1 and P2 call stored procedure P3. Stored procedure P3 references table T1. In this example, each of the procedures is dependent on table T1. P3 depends upon T1 directly, while P1 and P2 depend upon T1 indirectly.

Figure 10-1 Dependency Relationships

If P3 is altered, then P1 and P2 are marked as invalid immediately, if they are on the same server as P3. The compiled states of P1 and P2 contain records of the timestamp of P3. Therefore, if the procedure P3 is altered and recompiled, then the timestamp on P3 no longer matches the value that was recorded for P3 during the compilation of P1 and P2.

If P1 and P2 are on a client system, or on another Oracle Server in a distributed environment, then the timestamp information is used to mark them as invalid at runtime.

Disadvantages of the Timestamp Model

The disadvantage of this dependency model is that it is unnecessarily restrictive. Recompilation of dependent objects across the network are often performed when not strictly necessary, leading to performance degradation.

Furthermore, on the client side, the timestamp model can lead to situations that block an application from running at all, if the client-side application is built using PL/SQL version 2. Earlier releases of tools, such as Oracle Forms, that used PL/SQL version 1 on the client side did not use this dependency model, because PL/SQL version 1 had no support for stored procedures.

For releases of Oracle Forms that are integrated with PL/SQL version 2 on the client side, the timestamp model can present problems. For example, during the installation of the application, the application is rendered invalid unless the client-side PL/SQL procedures that it uses are recompiled at the client site. Also, if a client-side procedure depends on a server procedure, and if the server procedure is changed or automatically recompiled, then the client-side PL/SQL procedure must then be recompiled. Yet in many application environments (such as Forms runtime applications), there is no PL/SQL compiler available on the client. This blocks the application from running at all. The client application developer must then redistribute new versions of the application to all customers.

Signatures

To alleviate some of the problems with the timestamp-only dependency model, Oracle provides the additional capability of remote dependencies using signatures. The signature capability affects only remote dependencies. Local (same server) dependencies are not affected, as recompilation is always possible in this environment.

A signature is associated with each compiled stored program unit. It identifies the unit using the following criteria:

• The name of the unit (the package, procedure, or function name).

• The types of each of the parameters of the subprogram.

• The modes of the parameters (IN, OUT, IN OUT).

• The number of parameters.

• The type of the return value for a function.

The user has control over whether signatures or timestamps govern remote dependencies.

When the signature dependency model is used, a dependency on a remote program unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and if the signature of this subprogram has been changed in an incompatible manner.

For example, consider a procedure Get_emp_name stored on a server in Boston (BOSTON_SERVER). The procedure is defined as the following:

CREATE OR REPLACE PROCEDURE Get_emp_name (
   emp_number   IN  NUMBER,
   hire_date    OUT VARCHAR2,
   emp_name     OUT VARCHAR2) AS
BEGIN
   SELECT ename, to_char(hiredate, 'DD-MON-YY')
      INTO emp_name, hire_date
      FROM emp
      WHERE empno = emp_number;
END;

When Get_emp_name is compiled on BOSTON_SERVER, its signature, as well as its timestamp, is recorded.

Now, assume that on another server in California, some PL/SQL code calls Get_emp_name identifying it using a DBlink called BOSTON_SERVER, as follows:

CREATE OR REPLACE PROCEDURE print_ename (emp_number IN NUMBER) AS
   hire_date    VARCHAR2(12);
   ename        VARCHAR2(10);
BEGIN
   get_emp_name@BOSTON_SERVER(emp_number, hire_date, ename);
   dbms_output.put_line(ename);
   dbms_output.put_line(hire_date);
END;

When this California server code is compiled, the following actions take place:

• A connection is made to the Boston server.

• The signature of Get_emp_name is transferred to the California server.

• The signature is recorded in the compiled state of Print_ename.

At runtime, during the remote procedure call from the California server to the Boston server, the recorded signature of Get_emp_name that was saved in the compiled state of Print_ename gets sent to the Boston server, regardless of whether or not there were any changes.

If the timestamp dependency mode is in effect, then a mismatch in timestamps causes an error status to be returned to the calling procedure.

However, if the signature mode is in effect, then any mismatch in timestamps is ignored, and the recorded signature of Get_emp_name in the compiled state of Print_ename on the California server is compared with the current signature of Get_emp_name on the Boston server. If they match, then the call succeeds. If they do not match, then an error status is returned to the Print_name procedure.

Note that the Get_emp_name procedure on the Boston server could have been changed. Or, its timestamp could be different from that recorded in the Print_name procedure on the California server, possibly due to the installation of a new release of the server. As long as the signature remote dependency mode is in effect on the California server, a timestamp mismatch does not cause an error when Get_emp_name is called.

When Does a Signature Change?

Datatypes

A signature changes when you switch from one class of datatype to another. Within each datatype class, there can be several types. Changing a parameter datatype from one type to another within a class does not cause the signature to change.

Table 10-3 lists the classes of types.

Table 10-3 Datatypes

Modes

Changing to or from an explicit specification of the default parameter mode IN does not change the signature of a subprogram. For example, you change

PROCEDURE P1 (Param1 NUMBER);

to

PROCEDURE P1 (Param1 IN NUMBER);