Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
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.
Note:
The restrictions on user-defined function expressions specified in "Function Expressions" apply to theCALL
statement as well.See Also:
PL/SQL User's Guide and Reference for information on creating such routinePrerequisites
You must have EXECUTE
privilege on the standalone routine or on the type or package in which the routine is defined.
Syntax
call::=
routine_clause::=
object_access_expression::=
Semantics
You can execute a routine in two ways. You can issue a call to the routine itself by name, by using the routine_clause
, or you can invoke a routine inside the type of an expression, by using an object_access_expression
.
schema
Specify the schema in which the standalone routine, or the package or type containing the routine, resides. If you do not specify schema
, then Oracle Database assumes the routine is in your own schema.
type or package
Specify the type or package in which the routine is defined.
routine_clause
Specify the name of the function or procedure being called, or a synonym that resolves to a function or procedure.
When you call a member function or procedure of a type, if the first argument (SELF
) is a null IN
OUT
argument, then Oracle Database returns an error. If SELF
is a null IN
argument, then the database returns null. In both cases, the function or procedure is not invoked.
Restriction on Functions If the routine is a function, then the INTO
clause is required.
@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
, then Oracle Database looks in your local database.
See Also:
"Calling a Procedure: Example" for an example of calling a routine directlyobject_access_expression
If you have an expression of an object type, such as a type constructor or a bind variable, you can use this form of expression to call a routine defined within the type. In this context, the object_access_expression
is limited to method invocations.
See Also:
"Object Access Expressions" for syntax and semantics of this form of expression, and "Calling a Procedure Using an Expression of an Object Type: Example" for an example of calling a routine using an expression of an object typeargument
Specify one or more arguments to the routine, if the routine takes arguments.
Restrictions on Applying Arguments to Routines The argument
is subject to the following restrictions:
You cannot use named notation for argument
. You must use the SQL notation, which is to specify the parameter value but not the parameter name. For example,
CALL my_procedure(arg1 => 3, arg2 => 4) ...
results in an error. The correct notation is:
CALL my_procedure(3, 4) ...
The datatypes of the parameters passed by the CALL
statement must be SQL datatypes. They cannot be PL/SQL-only datatypes such as BOOLEAN
.
An argument
cannot be a pseudocolumn or either of the object reference functions VALUE
or REF
.
Any argument
that is an IN
OUT
or OUT
argument of the routine must correspond to a host variable expression.
The number of arguments, including any return argument, is limited to 1000.
You cannot bind arguments of character and raw datatypes (CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, RAW
, LONG
RAW
) that are larger than 4K.
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++ Programmer's Guide for more information on host variables and indicator variablesExample
Calling a Procedure: Example The following statement uses the remove_dept
procedure (created in "Creating a Package Body: Example") to remove the Entertainment department (created in "Inserting Sequence Values: Example"):
CALL emp_mgmt.remove_dept(162);
Calling a Procedure Using an Expression of an Object Type: Example The following examples show how call a procedure by using an expression of an object type in the CALL
statement. The example uses the warehouse_typ
object type in the order entry sample schema OE
:
ALTER TYPE warehouse_typ ADD MEMBER FUNCTION ret_name RETURN VARCHAR2 CASCADE; CREATE OR REPLACE TYPE BODY warehouse_typ AS MEMBER FUNCTION ret_name RETURN VARCHAR2 IS BEGIN RETURN self.warehouse_name; END; END; / VARIABLE x VARCHAR2(25); CALL warehouse_typ(456, 'Warehouse 456', 2236).ret_name() INTO :x; PRINT x; X -------------------------------- Warehouse 456
The next example shows how to use an external function to achieve the same thing:
CREATE OR REPLACE FUNCTION ret_warehouse_typ(x warehouse_typ) RETURN warehouse_typ IS BEGIN RETURN x; END; / CALL ret_warehouse_typ(warehouse_typ(234, 'Warehouse 234', 2235)).ret_name() INTO :x; PRINT x; X -------------------------------- Warehouse 234