Oracle7 Server Application Developer's Guide

Invoking Stored Procedures

Procedures can be invoked from many different environments. For example:

A procedure can be called within the body of another procedure or a trigger.

A procedure can be interactively called by a user using an Oracle tool (such as SQL*Plus)

A procedure can be explicitly called within an application (such as a SQL*Forms or precompiler application).

A stored function can be called from a SQL statement in a manner similar to calling a built-in SQL function, such as LENGTH or ROUND.

Some common examples of invoking procedures from within these environments follow. Calling stored functions from SQL is described

A Procedure or Trigger Calling Another Procedure

A procedure or trigger can call another stored procedure. For example, included in the body of one procedure might be the line

. . .
sal_raise(emp_id, 200);
. . .

This line calls the SAL_RAISE procedure. EMP_ID is a variable within the context of the procedure. Note that recursive procedure calls are allowed within PL/SQL; that is, a procedure can call itself.

Interactively Invoking Procedures From Oracle Tools

A procedure can be invoked interactively from an Oracle tool such as SQL*Plus. For example, to invoke a procedure named SAL_RAISE, owned by you, you can use an anonymous PL/SQL block, as follows:

BEGIN
    sal_raise(1043, 200);
END;

Note: Interactive tools such as SQL*Plus require that you follow these lines with a slash (/) to execute the PL/SQL block.

An easier way to execute a block is to use the SQL*Plus command EXECUTE, which effectively wraps BEGIN and END statements around the code you enter. For example:

EXECUTE sal_raise(1043, 200);

Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:

VARIABLE assigned_empno NUMBER

Once defined, any session variable can be used for the duration of the session. For example, you might execute a function and capture the return value using a session variable:

EXECUTE :assigned_empno := hire_emp('JSMITH', 'President', \
   1032, SYSDATE, 5000, NULL, 10);
PRINT assigned_empno;
ASSIGNED_EMPNO
--------------
          2893

See the SQL*Plus User's Guide and Reference for SQL*Plus information. See your tools manual for information about performing similar operations using your development tool.

Calling Procedures within 3GL Applications

A 3GL database application such as a precompiler or OCI application can include a call to a procedure within the code of the application.

To execute a procedure within a PL/SQL block in an application, simply call the procedure. The following line within a PL/SQL block calls the FIRE_EMP procedure:

fire_emp(:empno);

In this case, :EMPNO is a host (bind) variable within the context of the application.

To execute a procedure within the code of a precompiler application, you must use the EXEC call interface. For example, the following statement calls the FIRE_EMP procedure in the code of a precompiler application:

EXEC SQL EXECUTE
   BEGIN
      fire_emp(:empno);
   END;
END-EXEC;

:EMPNO is a host (bind) variable.

For more information about calling PL/SQL procedures from within 3GL applications, see the following manuals:

Programmer's Guide to the Oracle Call Interface

Programmer's Guide to the Oracle Pro*C/C++ Precompiler, for Pro*C/C++ release 2.2

For other precompilers, the Programmer's Guide to the Oracle Precompilers, plus the relevant language supplement, such as the Pro*COBOL Supplement to the Oracle Precompilers Guide or the Pro*FORTRAN Supplement to the Oracle Precompilers Guide.

SQL*Module User's Guide and Reference

Name Resolution When Invoking Procedures

References to procedures and packages are resolved according to the algorithm described in the section "Name Resolution in SQL Statements"

Privileges Required to Execute a Procedure

If you are the owner of a standalone procedure or package, you can execute the standalone procedure or packaged procedure, or any public procedure or packaged procedure at any time, as described in the previous sections. If you want to execute a standalone or packaged procedure owned by another user, the following conditions apply:

You must have the EXECUTE privilege for the standalone procedure or package containing the procedure, or have the EXECUTE ANY PROCEDURE system privilege. If you are executing a remote procedure, you must have been granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not via a role.

You must include the owner's name in the call, as in:

		EXECUTE jward.fire_emp (1043);

		EXECUTE jward.hire_fire.fire_emp (1043);

Attention: A stored subprogram or package executes in the privilege domain of the owner of the procedure. The owner must have been explicitly granted the necessary object privileges to all objects referenced within the body of the code.

Specifying Values for Procedure Arguments

When you invoke a procedure, specify a value or parameter for each of the procedure's arguments. Identify the argument values using either of the following methods, or a combination of both:

List the values in the order the arguments appear in the procedure declaration.

Specify the argument names and corresponding values, in any order.

For example, these statements each call the procedure UPDATE_SAL to increase the salary of employee number 7369 by 500:

sal_raise(7369, 500);

sal_raise(sal_incr=>500, emp_id=>7369);

sal_raise(7369, sal_incr=>500);

The first statement identifies the argument values by listing them in the order in which they appear in the procedure specification.

The second statement identifies the argument values by name and in an order different from that of the procedure specification. If you use argument names, you can list the arguments in any order.

The third statement identifies the argument values using a combination of these methods. If you use a combination of order and argument names, values identified in order must precede values identified by name.

If you have used the DEFAULT option to define default values for IN parameters to a subprogram (see the PL/SQL User's Guide and Reference), you can pass different numbers of actual parameters to the 1subprogram, accepting or overriding the default values as you please. If an actual value is not passed, the corresponding default value is used. If you want to assign a value to an argument that occurs after an omitted argument (for which the corresponding default is used), you must explicitly designate the name of the argument, as well as its value.

Invoking Remote Procedures

Invoke remote procedures using an appropriate database link and the procedure's name. The following SQL*Plus statement executes the procedure FIRE_EMP located in the database pointed to by the local database link named NY:

EXECUTE fire_emp@NY(1043);

For information on exception handling when calling remote procedures, see .

Remote Procedure Calls and Parameter Values

You must explicitly pass values to all remote procedure parameters even if there are defaults. You cannot access remote package variables and constants.

Referencing Remote Objects

Remote objects can be referenced within the body of a locally defined procedure. The following procedure deletes a row from the remote employee table:

CREATE PROCEDURE fire_emp(emp_id NUMBER) IS
BEGIN
    DELETE FROM emp@sales WHERE empno = emp_id;
END;

The list below explains how to properly call remote procedures, depending on the calling environment.

Remote procedures (standalone and packaged) can be called from within a procedure, OCI application, or precompiler application by specifying the remote procedure name, a database link, and the arguments for the remote procedure.

		CREATE PROCEDURE local_procedure(arg1, arg2) AS
		BEGIN
		   ...
	   remote_procedure@dblink(arg1, arg2);
		   ...
		END;

In the previous example, you could create a synonym for REMOTE_PROCEDURE@DBLINK. This would enable you to call the remote procedure from an Oracle tool application, such as a SQL*Forms application, as well from within a procedure, OCI application, or precompiler application.

		CREATE PROCEDURE local_procedure(arg1, arg2) AS
		BEGIN
		   ...
		   synonym(arg1, arg2);
		   ...
		END;

If you did not want to use a synonym, you could write a local cover procedure to call the remote procedure.

BEGIN local_procedure(arg1, arg2); END;

Here, LOCAL_PROCEDURE is defined as in the first item of this list. Note: Synonyms can be used to create location transparency for the associated remote procedures.

Warning: Unlike stored procedures, which use compile-time binding, when referencing remote procedures, runtime binding is used. The user account to which you connect depends on the database link.

All calls to remotely stored procedures are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote procedure is read-only). Furthermore, if a transaction that includes a remote procedure call is rolled back, the work done by the remote procedure is also rolled back. A procedure called remotely cannot execute a COMMIT, ROLLBACK, or SAVEPOINT statement.

A distributed update modifies data on two or more nodes. A distributed update is possible using a procedure that includes two or more remote updates that access data on different nodes. Statements in the construct are sent to the remote nodes and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating procedures that perform distributed updates.

Pay special attention when using a local procedure that calls a remote procedure. If a timestamp mismatch is found during execution of the local procedure, the remote procedure is not executed and the local procedure is invalidated.

Synonyms for Procedures and Packages

Synonyms can be created for standalone procedures and packages to

hide the identity of the name and owner of a procedure or package

provide location transparency for remotely stored procedures (standalone or within a package)

When a privileged user needs to invoke a procedure, an associated synonym can be used. Because the procedures defined within a package are not individual objects (that is, the package is the object), synonyms cannot be created for individual procedures within a package.