Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Procedures and Packages

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:

The information in this chapter applies only to those systems using Oracle with the procedural option. For information about the dependencies of procedures, functions, and packages, and how Oracle manages these dependencies, see Chapter 16, "Dependencies Among Schema Objects".

If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide.

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.

Stored Procedures and Functions

A procedure or function is a schema object that logically groups 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 14 - 1 illustrates a simple procedure stored in the database, being called by several different database applications.

Figure 14 - 1. A Stored Procedure

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

Figure 14 - 2. The HIRE_EMP Procedure

All of the database applications in Figure 14 - 1 call the HIRE_EMP procedure. Alternatively, a privileged user might use Server Manager 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.


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 14 - 3 illustrates a package that encapsulates a number of procedures used to manage an employee database.

Figure 14 - 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, the following statement might be issued using Server Manager 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. These advantages are described in the section "Packages" [*].


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 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 about PL/SQL, see the section "PL/SQL" [*].

Procedures and Functions

Oracle can process procedures and functions as well as individual SQL statements. 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; 
	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; 
	    INSERT INTO accounts (acct_id, balance) 
		VALUES(acct, credit); 
END credit_account; 

Notice that both SQL and PL/SQL statements are included in the CREDIT_ACCOUNT procedure.

Procedures and functions are nearly identical. The only differences are that functions always return a single value to the caller, while procedures do not. For simplicity, the term "procedure" is used in the remainder of this chapter to mean "procedures and functions," unless otherwise noted.

How Procedures Are Used

You should design and use all stored procedures so that they have the following properties:

Applications for Procedures

Procedures provide advantages in the following areas:


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.

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 that have only the privilege to execute the procedure and 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.


Stored procedures can improve database performance. Use of procedures dramatically reduces the amount of information that must be sent over a network 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. Furthermore, because a procedure's compiled form is readily available in the database, no compilation is required at execution time. Additionally, 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.


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.


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 vs. Stored Procedures

You create an anonymous PL/SQL block by sending an unnamed PL/SQL block to 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 subsequent reuse.

Shared SQL allows a compiled anonymous PL/SQL block already in the shared pool to be reused and shared until it is flushed out of the shared pool.

Alternatively, a stored procedure is created and stored in the database as an 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.

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

Standalone Procedures vs. Package 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.

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 16, "Dependencies Among Schema Objects", for more information about dependency tracking.


Packages provide a method of encapsulating related procedures, functions, and associated cursors and variables together as a unit in the database.

For example, the following two statements create the specification and body for a package that contains several procedures and functions that process banking transactions.

CREATE PACKAGE bank_transactions  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. */ 
	INSERT INTO journal 
	  VALUES (acct, kind, sysdate); 
	IF kind = 'D' THEN 
	  new_status := 'Debit applied'; 
	ELSIF kind = 'C' THEN 
	  new_status := 'Credit applied'; 
	  new_status := 'New account'; 
  END do_journal_entry; 

(continued next page)

  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; 
	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'); 
	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; 
	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'); 
	  RAISE insufficient_funds; 

(continued next page)

	  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; 
	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); 
	    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; 
	COMMIT;  /* Release row locks in TRANSACTIONS table. */ 
  END apply_transactions; 

(continued next page)

  PROCEDURE enter_transaction (acct   NUMBER, 
				kind   CHAR, 
				amount NUMBER) IS 
/* Enters a bank transaction into the TRANSACTIONS table. A new 
   transaction is always input into this 'queue' before being 
   applied to the specified account by the APPLY_TRANSACTIONS 
   procedure. Therefore, many transactions can be simultaneously 
   input without interference. */ 
	INSERT INTO transactions 
	  VALUES (acct, kind, amount, 'Pending', sysdate); 
  END enter_transaction; 
END bank_transactions; 

While packages allow the database administrator or application developer to organize similar routines, they also offer increased functionality and database performance.

Applications for Packages

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


Stored packages allow you to encapsulate, or group, related stored procedures, variables, datatypes, etc. 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


Directly accessible to the user of a package.


Hidden from the user of a package.

For example, a package might contain ten procedures. However, the package can be defined 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 17, "Database Access".

Separate Package Specification and Package Body

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:

Performance Improvement

Using packages rather than stand-alone stored procedures results in the following improvements:

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 16, "Dependencies Among Schema Objects", for more information about dependency tracking.

How Oracle Stores Procedures and Packages

When you create a procedure or package, Oracle automatically performs these steps:

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. Information on identifying compilation errors is contained in the Oracle7 Server Application Developer's Guide.

Storing the Compiled Code in Memory

Oracle caches the compiled procedure or package in the shared pool of the 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/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:

object name

Oracle uses this name to identify the procedure or package. You specify this name in the CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE, or CREATE PACKAGE BODY statement.

source code and parse tree

The PL/SQL compiler parses the source code and produces a parsed representation of the source code, called a parse tree.

pseudocode (P code)

The PL/SQL compiler generates the pseudocode, or P code, based on the parsed code. The PL/SQL engine executes this when the procedure or package is invoked.

error messages

Oracle might generate errors during the compilation of a procedure or package.

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. The database administrator should plan the size of the SYSTEM tablespace, keeping 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 performs these steps to execute it:

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 see if 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:

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 16, "Dependencies Among Schema Objects".

Executing a Procedure

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

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index