CALL

Purpose

Use the CALL statement to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL.

See Also: PL/SQL User's Guide and Reference for information on creating such routine

Prerequisites

You must have EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.

Syntax

Keywords and Parameters

schema

Specify the schema in which the standalone routine (or the package or type containing the routine) resides. If you do not specify schema, Oracle assumes the routine is in your own schema.

type or package

Specify the type or package in which the routine is defined.

function | procedure | method

Specify the name of the function or procedure being called, or a synonym that translates to a function or procedure.

When you call a type's member function or procedure, if the first argument (SELF) is a null IN OUT argument, Oracle returns an error. If SELF is a null IN argument, Oracle returns null. In both cases, the function or procedure is not invoked.

Restriction: If the routine is a function, the INTO clause is mandatory.

@dblink

In a distributed database system, specify the name of the database containing the standalone routine (or the package or function containing the routine). If you omit dblink, Oracle looks in your local database.

expr

Specify one or more arguments to the routine.

Restrictions:

• An expr cannot be a pseudocolumn or either of the object reference functions VALUE or REF.

• Any expr that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.

INTO :host_variable

The INTO clause applies only to calls to functions. Specify which host variable will store the return value of the function.

:indicator_variable

Specify the value or condition of the host variable.

See Also: Pro*C/C++ Precompiler Programmer's Guide for more information on host variables and indicator variables

Example

Calling a Procedure Example

The following statement creates a procedure updatesalary, and then calls the procedure, which updates the specified employee ID with a new salary.

CREATE OR REPLACE PROCEDURE updateSalary
   (id NUMBER, newsalary NUMBER) IS 
   BEGIN 
      UPDATE emp SET sal=newsalary WHERE empno=id; 
   END; 

CALL updateSalary(1404, 50000);