Procedures and Packages (2024)

We're dealing here with science, but it is science which has not yet been fully codified by scientific minds. What we have are the memoirs of poets and occult adventurers...

Anne Rice: The Tale of the Body Thief

This chapter discusses the procedural capabilities of Oracle. It includes:

• An Introduction to Stored Procedures and Packages
• Procedures and Functions
• Packages
• How Oracle Stores Procedures and Packages
• How Oracle Executes Procedures and Packages

For information about the dependencies of procedures, functions, and packages, and how Oracle manages these dependencies, see Chapter 21, "Oracle Dependency Management".

An Introduction to Stored Procedures and Packages

Oracle allows you to access and manipulate database information using procedural schema objects called PL/SQL program units. Procedures, functions, and packages are all examples of PL/SQL program units.

PL/SQL is Oracle's procedural language extension to SQL. It extends SQL with flow control and other statements that make it possible to write complex programs in it. The PL/SQL engine is the tool you use to define, compile, and execute PL/SQL program units. This engine is a special component of many Oracle products, including the Oracle server.

While many Oracle products have PL/SQL components, this chapter specifically covers the procedures and packages that can be stored in an Oracle database and processed using the Oracle server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's documentation. For more information, see "PL/SQL".

Stored Procedures and Functions

Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use. You can execute a procedure or function interactively using an Oracle tool, such as SQL*Plus, or call it explicitly in the code of a database application, such as an Oracle Forms or Precompiler application, or in the code of another procedure or trigger.

Figure 18-1 illustrates a simple procedure that is stored in the database and called by several different database applications.

Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, the term "procedure" as used in the remainder of this chapter means "procedure or function".

Figure 18-1 A Stored Procedure

The stored procedure in Figure 18-1, which inserts an employee record into the EMP table, is shown in Figure 18-2.

Figure 18-2 The HIRE_EMP Procedure

All of the database applications in Figure 18-1 call the HIRE_EMP procedure. Alternatively, a privileged user might use Oracle Enterprise Manager or SQL*Plus to execute the HIRE_EMP procedure using the following statement:

EXECUTE hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, \  500, NULL, 20); 

This statement places a new employee record for TSMITH in the EMP table.

Packages

A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.

Figure 18-3 illustrates a package that encapsulates a number of procedures used to manage an employee database.

Figure 18-3 A Stored Package

Database applications explicitly call packaged procedures as necessary. After being granted the privileges for the EMP_MGMT package, a user can explicitly execute any of the procedures contained in it. For example, Oracle Enterprise Manager or SQL*Plus might issue the following statement to execute the HIRE_EMP package procedure:

EXECUTE emp_mgmt.hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20); 

Packages offer several development and performance advantages over standalone stored procedures (see "Packages").

Procedures and Functions

A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and executed as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values.

Procedures and functions allow you to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. For example, the following statement creates the CREDIT_ACCOUNT procedure, which credits money to a bank account:

CREATE PROCEDURE credit_account  (acct NUMBER, credit NUMBER) AS /* This procedure accepts two arguments: an account number and an  amount of money to credit to the specified account. If the  specified account does not exist, a new account is created. */  old_balance NUMBER;  new_balance NUMBER;  BEGIN  SELECT balance INTO old_balance FROM accounts  WHERE acct_id = acct  FOR UPDATE OF balance;  new_balance := old_balance + credit;  UPDATE accounts SET balance = new_balance  WHERE acct_id = acct;  COMMIT;  EXCEPTION  WHEN NO_DATA_FOUND THEN  INSERT INTO accounts (acct_id, balance)  VALUES(acct, credit);  WHEN OTHERS THEN  ROLLBACK; END credit_account; 

Notice that this sample procedure includes both SQL and PL/SQL statements.

Procedure Guidelines

Use the following guidelines when designing stored procedures:

• Define procedures to complete a single, focused task. Do not define long procedures with several distinct subtasks, because subtasks common to many procedures might be duplicated unnecessarily in the code of several procedures.
• Do not define procedures that duplicate the functionality already provided by other features of Oracle. For example, do not define procedures to enforce simple data integrity rules that you could easily enforce using declarative integrity constraints.

Benefits of Procedures

Procedures provide advantages in the following areas.

Security

Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that execute with the definer's privileges. (See "Definer Rights and Invoker Rights".) For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.

Performance

Stored procedures can improve database performance in several ways:

• The amount of information that must be sent over a network is small compared to issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, because the information is sent only once and thereafter invoked when it is used.
• A procedure's compiled form is readily available in the database, so no compilation is required at execution time.
• If the procedure is already present in the shared pool of the SGA, retrieval from disk is not required, and execution can begin immediately.

Memory Allocation

Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.

Productivity

Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

For example, procedures can be written to insert, update, or delete rows from the EMP table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.

Integrity

Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, only the procedure needs to be recompiled; applications that call the procedure do not necessarily require any modifications.

Anonymous PL/SQL Blocks versus Stored Procedures

A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.

As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.

In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.

Standalone Procedures

Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package. (See "Packages" for information on the advantages of packages.)

Definer Rights and Invoker Rights

A PL/SQL procedure can be executed with the privileges of its owner (definer rights) or with the privileges of the current user (invoker rights), depending on the procedure definition.

• A definer-rights procedures executes with its definer's privileges. Roles are disabled in a definer-rights procedure.
• An invoker-rights procedure executes with all of the invoker's privileges, including enabled roles.

See "Procedure Security Topics" for more information about privileges, and see "PL/SQL Blocks and Roles" for more information about roles.

The Current User

When an invoker-rights procedure is the first program called in a software bundle, the invoker or current user is the session user, who is either the logged-in user or the user associated with the remote procedure call session. On entering another invoker-rights procedure, the current user does not change.

However, on entering a definer-rights procedure, the owner of that procedure becomes the current user. If the definer-rights procedure then calls an invoker-rights procedure, the current user remains the owner of the definer-rights procedure.

On exiting a definer-rights procedure, the current user reverts from the procedure's owner to the previous current user, that is, the current user of the procedure which called the definer-rights procedure.

Resolution of External References

An external reference in a PL/SQL procedure is something that refers to an object outside the program unit.

• For a definer-rights procedure, all external references are resolved in the schema that contains the procedure.
• For an invoker-rights procedure, external references are resolved differently depending on the kind of statement they appear in. The following names are resolved in the schema associated with the invoker.
  • names in DML statements, such as tables, views, and sequences
  • names in cursors
  • names in dynamic SQL statements and DBMS_SQL statements

  The names of program units that the invoker-rights procedure calls are resolved in the schema containing the procedure.

Name resolution in the invoker's schema allows applications to access user-specific tables by not specifying the schema. See "Name Resolution for Database Objects and Program Units" for more information.

Dependency Tracking for Stored Procedures

A stored procedure is dependent on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the definition of a table referenced by a procedure, the procedure must be recompiled to validate that it will continue to work as designed. Usually, Oracle automatically administers such dependency management.

See Chapter 21, "Oracle Dependency Management", for more information about dependency tracking.

External Procedures

A PL/SQL procedure executing on an Oracle server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine executes in a separate address space from that of the Oracle server.

Packages

Packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the database.

You create a package in two parts: the specification and the body. A package's specification declares all public constructs of the package and the body defines all constructs (public and private) of the package. This separation of the two parts provides the following advantages:

• The developer has more flexibility in the development cycle. You can create specifications and reference public procedures without actually creating the package body.
• You can alter procedure bodies contained within the package body separately from their publicly declared specifications in the package specification. As long as the procedure specification does not change, objects that reference the altered procedures of the package are never marked invalid; that is, they are never marked as needing recompilation. (For more information about dependencies, see Chapter 21, "Oracle Dependency Management".)

The following example creates the specification and body for a package that contains several procedures and functions that process banking transactions.

CREATE PACKAGE bank_transactions (null) AS  minimum_balance CONSTANT NUMBER := 100.00;  PROCEDURE apply_transactions;  PROCEDURE enter_transaction (acct NUMBER,  kind CHAR,  amount NUMBER); END bank_transactions; CREATE PACKAGE BODY bank_transactions AS /* Package to input bank transactions */  new_status CHAR(20); /* Global variable to record status  of transaction being applied. Used  for update in APPLY_TRANSACTIONS. */  PROCEDURE do_journal_entry (acct NUMBER,  kind CHAR) IS /* Records a journal entry for each bank transaction applied  by the APPLY_TRANSACTIONS procedure. */  BEGIN  INSERT INTO journal  VALUES (acct, kind, sysdate);  IF kind = 'D' THEN  new_status := 'Debit applied';  ELSIF kind = 'C' THEN  new_status := 'Credit applied';  ELSE  new_status := 'New account';  END IF;  END do_journal_entry;  PROCEDURE credit_account (acct NUMBER, credit NUMBER) IS /* Credits a bank account the specified amount. If the account  does not exist, the procedure creates a new account first. */  old_balance NUMBER;  new_balance NUMBER;  BEGIN  SELECT balance INTO old_balance FROM accounts  WHERE acct_id = acct  FOR UPDATE OF balance; /* Locks account for credit update */  new_balance := old_balance + credit;  UPDATE accounts SET balance = new_balance  WHERE acct_id = acct;  do_journal_entry(acct, 'C');  EXCEPTION  WHEN NO_DATA_FOUND THEN /* Create new account if not found */  INSERT INTO accounts (acct_id, balance)  VALUES(acct, credit);  do_journal_entry(acct, 'N');  WHEN OTHERS THEN /* Return other errors to application */  new_status := 'Error: ' || SQLERRM(SQLCODE);  END credit_account;  PROCEDURE debit_account (acct NUMBER, debit NUMBER) IS /* Debits an existing account if result is greater than the  allowed minimum balance. */  old_balance NUMBER;  new_balance NUMBER;  insufficient_funds EXCEPTION;  BEGIN  SELECT balance INTO old_balance FROM accounts  WHERE acct_id = acct  FOR UPDATE OF balance;  new_balance := old_balance - debit;  IF new_balance >= minimum_balance THEN  UPDATE accounts SET balance = new_balance  WHERE acct_id = acct;  do_journal_entry(acct, 'D');  ELSE  RAISE insufficient_funds;  END IF;  EXCEPTION  WHEN NO_DATA_FOUND THEN  new_status := 'Nonexistent account';  WHEN insufficient_funds THEN  new_status := 'Insufficient funds';  WHEN OTHERS THEN /* Returns other errors to application */  new_status := 'Error: ' || SQLERRM(SQLCODE);  END debit_account;  PROCEDURE apply_transactions IS /* Applies pending transactions in the table TRANSACTIONS to the  ACCOUNTS table. Used at regular intervals to update bank  accounts without interfering with input of new transactions. */ /* Cursor fetches and locks all rows from the TRANSACTIONS  table with a status of 'Pending'. Locks released after all pending transactions have been applied. */  CURSOR trans_cursor IS  SELECT acct_id, kind, amount FROM transactions  WHERE status = 'Pending'  ORDER BY time_tag  FOR UPDATE OF status;  BEGIN  FOR trans IN trans_cursor LOOP /* implicit open and fetch */  IF trans.kind = 'D' THEN  debit_account(trans.acct_id, trans.amount);  ELSIF trans.kind = 'C' THEN  credit_account(trans.acct_id, trans.amount);  ELSE  new_status := 'Rejected';  END IF;  /* Update TRANSACTIONS table to return result of applying  this transaction. */  UPDATE transactions SET status = new_status  WHERE CURRENT OF trans_cursor;  END LOOP;  COMMIT; /* Release row locks in TRANSACTIONS table. */  END apply_transactions;  PROCEDURE enter_transaction (acct NUMBER,  kind CHAR,  amount NUMBER) IS /* Enters a bank transaction into the TRANSACTIONS table. A new  transaction is always put into this 'queue' before being  applied to the specified account by the APPLY_TRANSACTIONS  procedure. Therefore, many transactions can be simultaneously  input without interference. */   BEGIN  INSERT INTO transactions  VALUES (acct, kind, amount, 'Pending', sysdate);  COMMIT;  END enter_transaction; END bank_transactions; 

Packages allow the database administrator or application developer to organize similar routines. They also offer increased functionality and database performance.

Benefits of Packages

Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the following areas:

• encapsulation of related procedures and variables
• declaration of public and private procedures, variables, constants, and cursors
• better performance

Encapsulation

Stored packages allow you to encapsulate (group) related stored procedures, variables, datatypes, and so forth in a single named, stored unit in the database. This provides for better organization during the development process.

Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.

Public and Private Data and Procedures

The methods of package definition allow you to specify which variables, cursors, and procedures are

For example, a package might contain ten procedures. You can define the package so that only three procedures are public and therefore available for execution by a user of the package; the remainder of the procedures are private and can only be accessed by the procedures within the package.

Do not confuse public and private package variables with grants to PUBLIC, which are described in Chapter 29, "Controlling Database Access".

Performance Improvement

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.

Dependency Tracking for Packages

A package is dependent on the objects referenced by the procedures and functions defined in its body. Oracle automatically tracks and manages such dependencies. See Chapter 21, "Oracle Dependency Management", for more information about dependency tracking.

Oracle Supplied Packages

Oracle supplies many PL/SQL packages that contain procedures for extending the functionality of the database or PL/SQL. Most of these packages have names that start with the "DBMS_" prefix, such as DBMS_SQL, DBMS_LOCK, and DBMS_JOB. Some supplied packages have the "UTL_" prefix, such as UTL_HTTP and UTL_FILE, or other prefixes including "DEBUG_" and "OUTLN_".

How Oracle Stores Procedures and Packages

When you create a procedure or package, Oracle

• compiles the procedure or package
• stores the compiled code in memory
• stores the procedure or package in the database

Compiling Procedures and Packages

The PL/SQL compiler compiles the source code. The PL/SQL compiler is part of the PL/SQL engine contained in Oracle. If an error occurs during compilation, a message is returned.

Storing the Compiled Code in Memory

Oracle caches the compiled procedure or package in the shared pool of the system global area (SGA). This allows the code to be executed quickly and shared among many users. The compiled version of the procedure or package remains in the shared pool according to the modified least-recently-used algorithm used by the shared pool, even if the original caller of the procedure terminates his or her session. See "The Shared Pool" for specific information about the shared pool buffer.

Storing Procedures or Packages in Database

At creation and compile time, Oracle automatically stores the following information about a procedure or package in the database:

To avoid unnecessary recompilation of a procedure or package, both the parse tree and the P code of an object are stored in the database. This allows the PL/SQL engine to read the compiled version of a procedure or package into the shared pool buffer of the SGA when it is invoked and not currently in the SGA. The parse tree is used when the code calling the procedure is compiled.

All parts of database procedures are stored in the data dictionary (which is in the SYSTEM tablespace) of the corresponding database. When planning the size of the SYSTEM tablespace, the database administrator should keep in mind that all stored procedures require space in this tablespace.

How Oracle Executes Procedures and Packages

When you invoke a standalone or packaged procedure, Oracle verifies user access, verifies procedure validity, and executes the procedure. The verification and execution differs for definer-rights procedures and invoker-rights procedures (see "Definer Rights and Invoker Rights").

Verifying User Access

Oracle verifies that the calling user owns or has the EXECUTE privilege on the procedure or encapsulating package. The user who executes a procedure does not require access to any procedures or objects referenced within the procedure; only the creator of a procedure or package requires privileges to access referenced schema objects.

Verifying Procedure Validity

Oracle checks the data dictionary to determine whether the status of the procedure or package is valid or invalid. A procedure or package is invalid when one of the following has occurred since the procedure or package was last compiled:

• One or more of the schema objects referenced within the procedure or package (such as tables, views, and other procedures) have been altered or dropped (for example, if a user added a column to a table).
• A system privilege that the package or procedure requires has been revoked from PUBLIC or from the owner of the procedure or package.
• A required schema object privilege for one or more of the schema objects referenced by a procedure or package has been revoked from PUBLIC or from the owner of the procedure or package.

A procedure is valid if it has not been invalidated by any of the above operations. If a valid standalone or packaged procedure is called, the compiled code is executed. If an invalid standalone or packaged procedure is called, it is automatically recompiled before being executed.

For a complete discussion of valid and invalid procedures and packages, recompiling procedures, and a thorough discussion of dependency issues, see Chapter 21, "Oracle Dependency Management".

Executing a Procedure

The PL/SQL engine executes the procedure or package using different steps, depending on the situation:

• If the procedure is valid and currently in memory, the PL/SQL engine simply executes the P code.
• If the procedure is valid and currently not in memory, the PL/SQL engine loads the compiled P code from disk to memory and executes it. For packages, all constructs of the package (all procedures, variables, and so on, compiled as one executable piece of code) are loaded as a unit.

The PL/SQL engine processes a procedure statement by statement, handling all procedural statements by itself and passing SQL statements to the SQL statement executor, as illustrated in Figure 16-2.

Name Resolution for Database Objects and Program Units

For a definer-rights procedure, all external references are resolved in the definer's schema. For an invoker-rights procedure, the resolution of external references depends on the kind of statement they appear in.

• External references in DML statements and dynamic SQL statements are resolved in the invoker's schema, and Oracle checks for access privileges at runtime using the invoker's rights. This rule applies to the names of database objects (such as tables, views, and sequences) in any of the following types of statements:
  • SELECT, UPDATE, INSERT, and DELETE statements
  • OPEN cursor (SELECT statements in a cursor declaration are resolved at OPEN)
  • LOCK TABLE statements
  • dynamic SQL statements: EXECUTE IMMEDIATE and PREPARE
  • DBMS_SQL statements: any statement parsed using DBMS_SQL.PARSE()

  Although the names of schema objects are resolved at run time, the compiler identifies a type for each such reference at compile time by temporarily resolving the name to a template object in the definer's schema.

• External references in all statements other than DML or dynamic SQL are resolved in the definer's schema, and Oracle checks for access privileges using the definer's rights at compile time. This rule applies to names of other program units called by the procedure (such as packages, procedures, functions, and types).

  For example, in an assignment statement involving an external function call "x := func(1)", the function name "func" resolves in the schema of the procedure's definer and the access to "func" is checked at compile time with the definer's privileges.

Name Resolution for Database Links

Database link names in PL/SQL procedures are resolved following the rules in the previous section. The authorization ID used to connect to the remote database is one of the following:

1. For named links, Oracle uses the username specified in the link to connect to the remote database. This behavior is the same in definer-rights procedures and invoker-rights procedures.

   CREATE DATABASE LINK link1  CONNECT TO scott IDENTIFIED BY tiger  USING connect_string; 

   If a procedure owned by JOE uses LINK1, no matter who invokes the procedure, the connection is as SCOTT because that is the name specified in the link.

2. For anonymous links, Oracle uses the session username to connect to the remote database. This behavior is the same in definer-rights procedures and invoker-rights procedures.

   CREATE DATABASE LINK link2  USING connect_string; 

   If a procedure owned by JOE uses an anonymous link LINK2, and a user SCOTT invokes the procedure, the connection to the remote database is as SCOTT.

3. For current user links, the behavior differs for definer-rights procedures and invoker-rights procedures:
   • For an invoker-rights procedure, Oracle uses the invoker's authorization ID to connect as a remote user.

     CREATE DATABASE LINK link3  CONNECT TO CURRENT_USER  USING connect_string; 

     If a global user SCOTT invokes the invoker-rights procedure owned by JOE, then LINK3 connects to the remote database as user SCOTT because SCOTT is the current user.

   • For a definer-rights procedure, Oracle uses the owner's authorization ID to connect as a remote user. If the definer-rights procedure is owned by JOE, LINK3 connects to the remote database as global user JOE who then becomes the current user.
     Note:

     The global user functionality that was available in Oracle8 is being modified, and is currently available to beta customers only. It will be part of Oracle8i in a later release.