1  CALL
 

2  Compound_Statement
   Invokes an external or stored procedure from within a compound
   statement. That is, invocation must occur with a BEGIN . . . END
   block.

   The OUT and INOUT arguments cannot be general value expressions.
   They must be variables or parameters. The IN argument can be a
   general value expression.

   When you register a procedure definition with the CREATE
   PROCEDURE statement, you store information in the database
   about an external procedure written in a 3GL language. External
   procedures reside outside the database. The CREATE PROCEDURE
   statement is documented under the CREATE Routine. See the CREATE
   Routine for more information on creating external procedures.

   For optional information on invoking stored procedures, see the
   CALL Simple_Statement.
 

3  Environment
   You can use the compound statement CALL:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

3  Format

 CALL --> <procedure-name> --> ( -+-------------------+-> ) -->
                                  +-+-> value-expr -+-+
                                    +-> DEFAULT ----+
                                    +------ , <-----+

 

3  Arguments
 

4  DEFAULT

   Requests that Oracle Rdb use the DEFAULT expression defined for
   the parameter. The DEFAULT can be defined for an IN parameter
   during the CREATE MODULE . . . PROCEDURE or ALTER MODULE . . . ADD
   PROCEDURE statements. If no DEFAULT clause exists then the NULL
   expression is assumed.
 

4  procedure-name

   The name of the external or stored procedure being invoked.
 

4  value-expr

   Any value expression except DBKEY or aggregate functions.

   See the Value_Expressions HELP topic for details on value
   expressions.
 

3  Examples
   Example 1: Calling an external routine within a compound
   statement

   BEGIN
      DECLARE :param1 INTEGER;
      CALL extern_routine (:param1, 3);
   END;
 

   Example 2: Calling a stored procedure from a stored function

   SQL> CREATE MODULE utility_functions
   cont>   LANGUAGE SQL
   cont> --
   cont>   PROCEDURE trace_date (:dt DATE);
   cont>      BEGIN
   cont>         TRACE :dt;
   cont>      END;
   cont> --
   cont>   FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10)
   cont>   COMMENT 'Returns the date in month/day/year format';
   cont>      BEGIN
   cont>         IF :dt IS NULL THEN
   cont>            RETURN '**/**/****';
   cont>         ELSE
   cont>            CALL trace_date (:dt);
   cont>            RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' ||
   cont>                   CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' ||
   cont>                   CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4));
   cont>         END IF;
   cont>      END;
   cont> END MODULE;
 

2  Simple_Statement
   Invokes a stored procedure.

   When you define a module with the CREATE MODULE statement, SQL
   stores the module as an object in an Oracle Rdb database. It also
   stores each of the module's procedures and functions. The module
   procedures that reside in an Oracle Rdb database are called
   stored procedures. In contrast, nonstored procedures refer to
   module procedures that reside outside the database in SQL module
   files. See the CREATE MODULE for more information on creating
   stored procedures.

   For optional information on invoking stored procedures, see the
   CALL Compound_Statement.
 

3  Environment
   You can use the simple statement CALL:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement that are dynamically executed
 

3  Format

 CALL --> <stored-procedure-name> --> call-argument-list -->
 



 call-argument-list =
 
 ---> ( --+-+------------------------+-+-> ) -->
          | +--> <literal> ----------+ |
          | +--> <parameter> --------+ |
          | +--> <variable> ---------+ |
          +----------- , <-------------+
 

 

3  Arguments
 

4  call-argument-list

   Passes a list of literal, parameter values (parameter markers for
   dynamic execution), or variables to the called stored procedure.

   You can pass a literal only to an IN parameter of a stored
   procedure. You cannot pass a literal to an OUT or INOUT
   parameter.

   In SQL statements to be dynamically executed, you refer to both
   the main and indicator parameters with a single parameter marker
   (?).  See the Parameters HELP topic for details about how to
   use parameters in programs for static as well as dynamic SQL
   statement execution.
 

4  procedure-name

   The name of a stored procedure.
 

3  Examples
   Example 1: Calling a stored procedure

   The following examples show the definition of a stored procedure,
   NEW_SALARY_PROC, and the nonstored procedure, CALL_NEW_SALARY,
   that invokes it with the simple statement CALL.

   SQL> ! The following shows the definition of the stored procedure:
   SQL> !
   SQL> CREATE MODULE NEW_SALARY_PROC
   cont>        LANGUAGE SQL
   cont>        PROCEDURE NEW_SALARY_PROC
   cont>                  (:ID CHAR (5),
   cont>                   :NEW_SALARY INTEGER (2));
   cont>         BEGIN
   cont>              UPDATE SALARY_HISTORY
   cont>                     SET SALARY_END = CURRENT_TIMESTAMP
   cont>                     WHERE EMPLOYEE_ID = :ID;
   cont>              INSERT INTO SALARY_HISTORY
   cont>                                 (EMPLOYEE_ID, SALARY_AMOUNT,
   cont>                                  SALARY_START, SALARY_END)
   cont>                          VALUES (:ID, :NEW_SALARY,
   cont>                                  CURRENT_TIMESTAMP, NULL);
   cont>         END;
   cont> END MODULE;
   SQL>

   The following example shows an excerpt of an SQL module that
   contains the nonstored procedure that calls the stored procedure.

      .
      .
      .
   PROCEDURE CALL_NEW_SALARY
       :ID CHAR(5),
       :ID_IND SMALLINT,
       :NEW_SALARY INTEGER (2),
       :NEW_SALARY_IND SMALLINT,
       SQLCODE;

       CALL NEW_SALARY_PROC (:ID, :NEW_SALARY );
      .
      .
      .

   Example 2: Calling a procedure in interactive SQL

   The following example shows that you use interactive SQL to
   invoke a stored procedure with the simple statement CALL:

   SQL> DECLARE :X INTEGER;
   SQL>  BEGIN
   cont>   SET :X = 0;
   cont> END;
   SQL> CALL P2 (10, :X);