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.

1  –  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

2  –  Format

  (B)0CALL qq> <stored-procedure-name> qq> call-argument-list qq> 
                                                              

  (B)0call-argument-list =                                
                                                      
  qqq> ( qqwqwqqqqqqqqqqqqqqqqqqqqqqqqwqwq> ) qq>     
           x tqq> <literal> qqqqqqqqqqu x             
           x tqq> <parameter> qqqqqqqqu x             
           x mqq> <variable> qqqqqqqqqj x             
           mqqqqqqqqqqq , <qqqqqqqqqqqqqj             
                                                      

3  –  Arguments

3.1  –  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.

3.2  –  procedure-name

    The name of a stored procedure.

4  –  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);