CALL

Use the CALL statement to execute a TimesTen built-in procedure or to execute a PL/SQL procedure or function that is standalone or part of a package from within SQL.

Required Privilege

The privileges required for executing each TimesTen built-in procedure are listed in the description of each procedure in the Built-In Procedures section in the Oracle TimesTen In-Memory Database Reference.

No privileges are required for an owner calling its own PL/SQL procedure or function that is standalone or part of a package using the CALL statement. For all other users, the EXECUTE privilege on the procedure or function or on the package in which it is defined is required.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

To call a TimesTen built-in procedure:

CALL TimesTenBuiltIn [( arguments )]

When calling PL/SQL procedures or functions that are standalone or part of a package, you can either call these by name or as the result of an expression.

To call a PL/SQL procedure:

CALL [Owner.][Package.]ProcedureName [( arguments )]

To call a PL/SQL function that returns a parameter, one of the following are appropriate:

CALL [Owner.][Package.]FunctionName [( arguments )] INTO :return_param

Note:

A user's own PL/SQL procedure or function takes precedence over a TimesTen built-in procedure with the same name.

Parameters

Parameter	Description
`TimesTenBuiltIn`	Name of the TimesTen built-in procedure. For a full list of TimesTen built-in procedures, see Built-In Procedures in the Oracle TimesTen In-Memory Database Reference.
`[Owner.]ProcedureName`	Name of the PL/SQL procedure. You can optionally specify the owner of the procedure.
`[Owner.]FunctionName`	Name of the PL/SQL function. You can optionally specify the owner of the function.
`arguments`	Specify 0 or more arguments for the PL/SQL procedure or function.
`INTO`	If the routine is a function, the `INTO` clause is required.
`return_param`	Specify the host variable that stores the return value of the function.

Description

Detailed information on how to execute PL/SQL procedures or functions with the CALL statement in TimesTen is provided in Executing Procedures and Functions in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide, Using CALL to Execute Procedures and Functions in the Oracle TimesTen In-Memory Database C Developer's Guide, or Using CALL to Execute Procedures and Functions in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Examples

The following is the definition of the mytest function:

create or replace function mytest return number is
begin
  return 1;
end;
/

Perform the following to execute the mytest function in a CALL statement:

Command> variable n number;
Command> call mytest() into :n;
Command> print n;
N                    : 1

The following example creates a function that returns the salary of the employee whose employee ID is specified as input, then calls the function and displays the result that was returned.

Command> CREATE OR REPLACE FUNCTION get_sal
          (p_id employees.employee_id%TYPE) RETURN NUMBER IS
            v_sal employees.salary%TYPE := 0;
         BEGIN
           SELECT salary INTO v_sal FROM employees
             WHERE employee_id = p_id;
           RETURN v_sal;
         END get_sal;
         /
 
Function created.
 
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N                    : 24000