In procedures, the RETURN statement cannot contain an expression. Its sole purpose is to return control to the calling program before the end of the procedure is reached.

Procedure Dependencies

One of the inherent features of Oracle is that it will check the database to make sure that the operations of a procedure, function, or package are possible based on the objects the user has access to. For example, if you have a procedure that requires access to several tables and views, Oracle will check during compilation time to see whether those tables and views are present and available to the user. The procedure is said to be dependent on these tables and views.

Discovering Dependencies

You can discover object dependencies in several different ways. You can examine the procedure or function code and determine which database objects it depends on. Also, you can talk with the DBA and examine the schema to identify dependencies. Finally, you can run the Oracle utldtree.sql script. This script will generate a temp table and a view that lets you see the objects that are dependent on a given object. This script will only generate a listing for those objects to which you have access.

Recompiling a Stored Procedure

To explicitly recompile a stored procedure, issue the ALTER PROCEDURE command. This command must only be used on standalone stored procedures and not on procedures that are part of the package.

Recompiling a procedure does not change the procedure's declaration or definition. You must use the CREATE PROCEDURE with the OR REPLACE clause to do these. If Oracle successfully recompiles a procedure, then the procedure becomes a valid procedure that can be executed without runtime compilation. If compilation fails, the procedure becomes invalid and must be debugged.

You can use the ALTER PROCEDURE command to explicitly recompile a procedure that is invalid. After a procedure is compiled, it does not need to be recompiled implicitly during runtime processes. This leads to reduced overhead and elimination of runtime compilation errors.

You can produce debugging information from within a application by issuing the PUT or PUT_LINE commands. These commands place the debugging information into a buffer that was created by the DBMS_OUTPUT package. To display the contents of the buffer, simply type the SET SERVEROUTPUT ON command at the SQL*Plus prompt.

The code in Listing 8.2 illustrates the PUT_LINE command line that you can include inside your procedure.

INPUT
Listing 8.2. PUT_LINE command within a procedure.

CREATE PROCEDURE parts (part_id number, qty number)
AS BEGIN
UPDATE journal;
PUT_LINE (`Original Qty =' || journal.qty);      -- debug Line
set journal.qty = journal.qty + qty
WHERE journal_id = part_id;
PUT_LINE (`New Qty =' || journal.qty);           -- debug Line

The following statements are issued at the SQL*Plus command line to execute the parts procedure and to display the debugging information:

SQL> set server on
SQL> execute user_01.parts

The following are the results of these statements being executed. This information is generated from the dba_output buffer area:

Original Qty = 100
New Qty = 200
Original Qty = 200
New Qty = 325
... and so on

Re-creating and Modifying Procedures

A valid, standalone procedure cannot be altered; it must be either replaced with a new definition or dropped and re-created. For example, you cannot just slightly alter one of the PL/SQL statements in the procedure. Instead, you must re-create the procedure with the modification.

When replacing a procedure, you must include the OR REPLACE clause in the CREATE PROCEDURE statement. The OR REPLACE clause is used to replace an older version of a procedure with a newer version of the procedure. This replacement keeps all grants in place; therefore, you will not have to re-create the grants. However, if you drop the procedure and re-create it, the grants are dropped and consequently will have to be rebuilt. If you attempt a CREATE PROCEDURE command for a procedure that already exists, Oracle will generate an error message.

Listing 8.3 re-creates the procedure named parts.

INPUT
Listing 8.3. Re-creating a procedure.

CREATE OR REPLACE PROCEDURE parts
(part_id number, qty number)
AS BEGIN
UPDATE journal
set journal.qty = (journal.qty*.05) + qty
WHERE journal_id = part_id
END;

Invoking Stored Procedures

Procedures can be invoked from many different environments including SQL*Plus and Oracle Forms. Also, procedures can be invoked from within another procedure or trigger.

For example, the procedure parts_sum can be called from with another procedure or trigger with the following statement:

...                        -- other PL/SQL block code
...
parts_sum(qty, wip_nbr);   -- calls parts_sum procedure
...

Another example where the same procedure is executed from within SQL*Plus is the following:

SQL> execute parts_sum(qty, wip_nbr);

The following example shows a procedure being called from within a precompiler program:

exec sql execute
BEGIN
parts_sum(qty, :wip_nbr)
END
END-exec

Parameters

Procedures use parameters (variables or expressions) to pass information. When a parameter is being passed to a procedure, it is known as an actual parameter. Parameters declared internal to a procedure are known as internal or formal parameters.

The actual parameter and its corresponding formal parameter must belong to compatible datatypes. For example, PL/SQL cannot convert an actual parameter with a datatype of DATE to a formal parameter with a datatype of LONG. In this case, Oracle would return an error message. This compatibility issue also applies to the return values.