|Oracle® Database SQL Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
Note:The restrictions on user-defined function expressions specified in "Function Expressions" apply to the
CALLstatement as well.
See Also:PL/SQL User's Guide and Reference for information on creating such routine
You must have
EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.
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
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.
Specify the type or package in which the routine is defined.
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
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.
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 directly
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 type
Specify one or more arguments to the routine, if the routine takes arguments.
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
argument cannot be a pseudocolumn or either of the object reference functions
argument that is an
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 (
RAW) that are larger than 4K.
INTO clause applies only to calls to functions. Specify which host variable will store the return value of the function.
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 variables
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"):
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
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