CALL - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

CALL

Purpose  

Invokes an SQL procedure or external stored procedure.

Syntax  

where:

 

Syntax Element …

Specifies …

database_name

user_name

optional qualifier for the SQL procedure or external procedure to be executed.

If database_name is not specified, the current default database is assumed.

procedure_name

the name of the SQL procedure or external procedure to be executed.

value_expression

Teradata Database-supported arithmetic and string expressions.

The following can be specified in a value expression, subject to client-specific restrictions:

  • SQL procedure local variables
  • SQL procedure status variables
  • IN or INOUT parameters
  • FOR loop columns and aliases
  • Host variables and macro parameters
  • FORMAT, TITLE, and NAMED phrases
  • Scalar UDFs
  • Scalar subqueries
  • For client-specific rules, see “Rules For Calling Procedures From Embedded SQL”.

    ?

    a call parameter argument.

    A QUESTION MARK character as an input call argument is valid only in SQL DML, ODBC, and JDBC client applications.

    out_call_variable

    an identifier prefixed with the COLON (:) character.

    Depending on the calling utility, the out_call_variable can be one of these:

  • host variable
  • local variable
  • IN or INOUT parameter
  • out_call_placeholder

    a parameter name.

    The placeholder provides for nesting of placeholders (parameters or CAST … AS clauses).

    parameter_name

    the name of the OUT parameter as defined in the SQL procedure.

    CAST … AS

    the request to convert the data definition of a parameter or another CAST clause to the required type. CAST clauses can be nested.

    FORMAT, NAMED and TITLE clauses can be used with the CAST operator.

    data_type

    the data definition for the parameter set. All Teradata Database-supported data types are valid.

    For detailed information about data types, see SQL Data Types and Literals.

    ANSI Compliance

    CALL is ANSI SQL:2011-compliant.

    Required Privileges

    You must have the EXECUTE PROCEDURE privilege on the procedure or its containing database or user.

    Users need not have any privileges on the database objects referenced by a called procedure. The privileges are checked for the immediate owner of the called procedure.

    The immediate owner must have the privileges on the referenced objects WITH GRANT OPTION to allow other users to access the object.

    For more information, see SQL Data Definition Language and Database Administration.

    Privileges Granted Automatically

    If you call the SQLJ.Install_Jar procedure to install a JAR file containing classes that implement a Java external stored procedure, the DROP PROCEDURE privilege is automatically granted to the creator of the JAR file (the caller of SQLJ.Install_Jar).

    Invocation

    Interactive SQL, embedded SQL, SQL procedures, and macros.

    Rules for Executing SQL Procedures and External Stored Procedures

    The following rules apply to executing SQL procedures and external procedures:

  • SQL procedures and external procedures are platform-specific. A procedure created under one platform can only be executed on that platform.
  • CALL can be performed only in Teradata or ANSI session modes.
  • If an SQL procedure is created in Teradata session mode, it cannot be executed in ANSI session mode and vice versa.

    CALL cannot be used in 2PC (Two Phase Commit) mode.

  • Additionally, CALL can be issued in Prepare or Execute mode using CLI applications.
  • LOCKING modifiers cannot be used with a CALL statement.
  • You can submit a CALL statement from a macro if it is the only statement inside the macro.
  • Only one nested procedure call can be an external procedure, the rest must be SQL procedures. For example, an external procedure cannot call an SQL procedure that in turn calls an external procedure.
  • Rules For Specifying Input And Output Parameters

    Call arguments consisting of input and output parameters must be submitted with a CALL statement. No default parameter values can be defined at the time a procedure is created; the CALL returns an error if the required call arguments are not specified.

    The following rules apply to the parameter arguments:

  • The number of call arguments in a CALL statement must be equal to the number of parameters in the called procedure.
  • If the called procedure has no parameters, you cannot specify any call arguments.

  • An IN, INOUT or OUT argument must correspond to an IN, INOUT or OUT parameter respectively in the called procedure.
  • The default data type for an INOUT parameter input value is determined by the narrowest type that can contain the data passed to the INOUT parameter at run time, not by the default type defined for the parameter in the “CREATE PROCEDURE (External Form)” or CREATE PROCEDURE (SQL Form)” statement that created the procedure.
  • To ensure that memory overflow errors do not occur when the size of an output value passed to an INOUT parameter exceeds the capacity of the default data type derived from the input value for that parameter, you must take either of the following precautions:

  • Explicitly cast the INOUT parameter data type in the CALL statement to ensure that memory overflow errors do not occur.
  • For example, if you submit the following CALL statement, the system determines that the smallest data type that can contain the input value to the INOUT parameter is SMALLINT, which requires 2 bytes, so it assigns the SMALLINT type to the parameter irrespective of the data type assigned to it at the time the procedure was created.

         CALL my_proc(32767);

    However, if the call returns a value greater than or equal to 32,768 to the INOUT parameter, the statement aborts unless you cast its type to the type that was assigned to the parameter at the time the procedure was created, or at minimum to INTEGER (the INTEGER type can contain positive values as large as 2,147,483,647, the BIGINT type can contain positive values as large as 9,223,372,036,854,775,807, the DECIMAL/NUMERIC type can contain far larger numbers, and so on. For details, see SQL Data Types and Literals and Database Design. For example, you might rewrite the statement as follows:

         CALL my_proc (CAST('32767' AS INTEGER));
  • Code the calling application to ensure the appropriate type conversion is made. For a JDBC example, see subexample 4 in “Example 5: Preventing Memory Overflow Errors For INOUT Parameters” on page 328.
  • For details, see “CREATE PROCEDURE (External Form)” or “CREATE PROCEDURE (SQL Form)” in SQL Data Definition Language.

    You must ensure either that the default data type for an INOUT parameter can accommodate the value or to know what type to cast it to in order to ensure that a memory overflow error does not occur. For information about how to determine the size of a literal, see “Data Literals” in SQL Data Types and Literals

  • Some additional rules and restrictions apply when CALL is submitted from various client utilities. See the following for the specific rules and restrictions:
  • “Rules For Call Arguments In BTEQ And CLIv2” on page 317
  • “Rules For Calling Procedures From Embedded SQL” on page 319
  • “Rules For Call Arguments In ODBC And JDBC” on page 318
  • “Rules For Call Arguments In Nested Procedures” on page 319
  • IN parameters can only have input, and OUT parameters can only have output. INOUT parameters can have both input and output.
  • The data type of a call argument must be compatible with the data type of the corresponding parameter in the called procedure.
  • A value expression of NULL can be used to assign NULL to the corresponding IN or INOUT parameter in the called procedure.
  • On successful completion of the procedure execution, the ACTIVITY_COUNT in the success response is set to the following values:
  •  

    ACTIVITY_COUNT is set to this value …

    IF the procedure has …

    1

    any output (INOUT or OUT) parameters.

    0

    no output parameters.

    Rules For Call Arguments In BTEQ And CLIv2

    These rules apply to call arguments submitted from applications in BTEQ or CLIv2:

  • An IN or INOUT argument must be a value expression.
  • In a value expression used as IN or INOUT argument, identifiers prefixed by the colon (:), if any, must refer to USING variables associated with a USING clause for the statement containing the CALL. The value of the expression is treated as the input value for the corresponding parameter in the called procedure.
  • An OUT argument can be any of the following:
  • An output host variable.
  • A place holder such as a QUESTION MARK character.
  • Any name other than the OUT parameter name specified in the procedure definition.
  • These rules apply to using ? parameters.
  • The following procedure call returns AMBIGUOUS, AMBIGUOUS as the information for the parameters p1 and resutl_1 with the expectation that during the execution phase, additional information will be forthcoming about the type of the parameter p1, which will determine the resulting type of result_1 parameter.

         CALL xsp2 (?, ?);

    This following procedure call fails because there is no expectation that there will be additional information about what data type the result_1 parameter should return.

         CALL xsp1 (?, ?);

    These examples show variations in the specification of OUT parameter names in a CALL statement issued from BTEQ. They s are based on the following CREATE PROCEDURE statement:

         CREATE PROCEDURE sp2 (OUT po1 INTEGER)
         BEGIN
           SET :po1 = 20;
         END;

    In the following CALL statement, the OUT argument name p1 differs from the OUT parameter name po1 specified in the procedure definition:

         CALL sp2(p1);
     
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.

    The same CALL statement also works if you specify a placeholder character or host variable instead of the explicit parameter specification, as the two following examples show:

         CALL sp2(?);
     
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.
     
         CALL sp2(:tx1);
     
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.

    Rules For Call Arguments In ODBC And JDBC

    The following additional rules apply to a call argument when the CALL statement is submitted from an ODBC or JDBC application:

  • An IN or INOUT argument must be one of the following:
  • A value expression.
  • A value expression must not contain identifiers prefixed by the COLON character. It must be a constant expression.

  • A QUESTION MARK (?) character used as an input placeholder.
  • If you specify ?, the value for the corresponding IN or INOUT parameter of the called procedure must be set using ODBC‑ or JDBC‑specific calls prior to calling the procedure.

    There is a 1:1 correspondence between the number of ? markers for IN and INOUT arguments and the number of data items specified in the StatementInfo parcel in the request message. StatementInfo does not contain entries for OUT arguments.

    For example, consider the following SQL procedure definition and CALL statement:

         CREATE PROCEDURE sp3 (
           IN pil     INTEGER,
           INOUT pio1 INTEGER,
           OUT po1    INTEGER)
         BEGIN
           SELECT j INTO :pio1
           FROM tb11
           WHERE i=2;
           SELECT k INTO :po1
           FROM tb11
           WHERE i=2;
         END;
     
         CALL sp3 (:?, :?, :?);

    When this call is made, the StatementInfo parcel contains 2 entries: one each for the IN and INOUT parameters.

  • An OUT argument must be an OUT call placeholder.
  • Rules For Call Arguments In Nested Procedures

    The following additional rules apply to a call argument when the CALL statement is submitted from another stored procedure:

  • An IN argument must be a value expression.
  • Identifiers in the value expression prefixed by the COLON character (:), if any, must refer to local variables, status variables, IN and INOUT parameters, or for-loop variable qualified columns and aliases of the calling procedure.

  • An INOUT argument must be a value expression that is limited to the form of an OUT-call-variable. The identifier must be prefixed with a COLON character and must refer to a local variable or an INOUT parameter of the calling procedure.
  • An OUT argument must be an OUT_call_variable. The identifier must refer to a local variable or an INOUT or OUT parameter.
  • Rules For Calling Procedures From Embedded SQL

    The following additional rules apply to a call argument when the CALL statement is submitted from a PP2 embedded SQL application:

  • An IN argument must be a value expression.
  • Identifiers in the value expression prefixed by the COLON character (:), if any, must refer to host variables. The value of the expression is treated as the input value for the corresponding parameter.

  • An INOUT argument must be a value expression that is limited to the form of an
    OUT_call_variable. The identifier must refer to a host variable.
  • An OUT argument must be an OUT_call_variable. The identifier must refer to a host variable.
  • Rules for Calling a Procedure With Dynamic Result Sets From Embedded SQL

    To be able to access dynamic result sets returned by a procedure called from embedded SQL, you must ensure that two preprocessor options are set properly.

  • Set the SQLCHECK option to FULL so the precompiler can pass the variables that receive the procedure OUT or INOUT parameters to the runtime phase of the preprocessor.
  • The following table indicates how to set this option for mainframe‑attached and workstation‑attached environments:

     

    TO enable SQL syntax, object reference, and privilege checking to FULL on this type of system …

    Specify the following option …

    mainframe‑attached

    SQLCHECK(FULL)

    workstation‑attached

    -sc FULL

  • If you define an embedded SQL cursor for a called procedure, you must also set the preprocessor TRANSACT option to BTET because CALL statements cannot be executed as part of a multistatement request in ANSI session mode.
  • The following table indicates how to set this option for mainframe‑attached and workstation‑attached environments:

     

    TO set the transaction mode to BTET on this type of system …

    Specify the following option …

    mainframe‑attached

    TRANSACT(BTET)

    workstation‑attached

    -tr BTET

    See “Example 8: Calling a Stored Procedure That Returns Dynamic Result Sets From Embedded SQL” on page 331.

    Session Dateform and Called Procedures

    Called procedures retain the date and time formats in effect when they were created. They do not reflect the dateform in effect for the session in which they are called unless it is the same as the dateform in effect when the procedure was created.

    See “SET SESSION DATEFORM” in SQL Data Definition Language Syntax and Examples.

    Retrieving Values of Output Parameters

    The output values of INOUT and OUT parameters are returned after completion of the CALL statement in the following forms:

     

    WHEN CALL is issued from this type of application …

    THEN the output parameter values are …

    BTEQ

    a result row.

    The values for all the INOUT and OUT parameters are returned in one row, irrespective of the number of parameters.

    The value of each parameter is a field in the output.

    CLIv2

    embedded SQL

    stored in the corresponding host variable.

    another procedure

    stored in the corresponding local variable or parameter of the calling procedure.

    ODBC

    to be retrieved using an ODBC API.

    JDBC

    to be retrieved using a JDBC API.

    Status of Unqualified Referenced Objects

    If the objects referenced in the called procedure are not qualified by a database name, then they are qualified by the name of the default database at the time the procedure was created.

    Dropped, Renamed, or Replaced Objects

    Consider the scenario when a procedure references another procedure that has been dropped or renamed.

    If the called procedure X references another procedure Y, and Y is later dropped, renamed, or replaced before executing X, the following occurs:

     

    In this session mode …

    This condition is generated when X submits an internal CALL to execute Y …

    ANSI

    Error

    Teradata

    Failure

    If Y is replaced, then its execution generates an error in ANSI session mode or a failure in Teradata session mode if the specified arguments are not compatible with the changed definition of Y.

    The following cases cause an error or failure:

  • The number of call arguments is not equal to the number of parameters in the changed definition of Y.
  • The data type of the call arguments is incompatible with the changed parameters of Y.
  • These conditions also apply to the attributes of any referenced database objects.

    Java External Procedure-Specific Behavior

    The system performs the following actions on the dictionary tables referenced by the SQLJ database during a CALL statement that executes a Java external procedure:

    1 Validates the Java procedure to be called.

    2 Retrieves the row matching the Java procedure name from the DBC.Routine_Jar_Usage table to determine which JAR must be loaded to execute this Java procedure.

    3 Verifies that the JAR named in DBC.Routine_Jar_Usage exists by finding the corresponding row in the DBC.Jars table.

    4 Retrieves any rows from the DBC.Jar_Jar_Usage table that indicate whether this JAR includes other JARs, and makes sure those JARs are also loaded.

    Called Procedure Priority

    A procedure executes at the priority the user session is running at the time the CALL statement is submitted. If you specify a SET SESSION ACCOUNT at a REQUEST level and the CALL statement is submitted, the procedure along with the SQL statements within the procedure execute at the specified priority.

    The account is set to the previous account after the CALL statement is completed.

    If the user priority is changed asynchronously during procedure execution, the new priority takes effect for all the subsequent requests that are submitted as part of procedure execution.

    If the asynchronous change is at the request level, the priority change applies to all the statements executed as part of the CALL statement.

    Consider the following SQL procedure:

         CREATE PROCEDURE prio2()
         BEGIN
           INSERT INTO temp(1, 'stored procedure before prio1') /* STMT1 */;
           CALL prio1()                                         /* STMT2 */;
           INSERT INTO temp(2, 'stored procedure after prio1')  /* STMT3 */;
         END;

    Scenarios

    The following three scenarios explain the priority scheduling for the procedure execution:

    Scenario  

         LOGON user1/user1, acct1;
         CALL prio2() /* this, along with the SQL statements inside */;
                      /* the procedure are executed at the   */;
                      /* priority associated with acct1             */;

    Scenario  

         LOGON user1/user1, acct1;
         CALL prio2() /* this, along with the SQL statements inside */;
                      /* the procedure are executed at the   */;
                      /* priority associated with acct1             */;
         SET SESSION ACCOUNT acct2 FOR REQUEST;
         CALL prio2() /* this, along with the SQL statements inside */; 
                      /* the procedure are executed at the   */;
                      /* priority associated with acct2             */;
         SELECT * FROM temp /* this is executed at the priority     */;
                            /* associated with acct1                */;

    Scenario  

    Assume that the priority is changed for user1 to acct2, after executing the STMT 1. The STMT 2 and STMT 3 (along with the SQL requests inside prio1) execute in the changed priority. If the priority is changed at request level, the session priority is restored to that corresponding to acct1 at the end of the execution of prio2.

         LOGON user1/user1, acct1;
         CALL prio2() /* this is executed at the priority associated */;
                      /* with acct1                                  */;

    Errors and Failures in Procedure Execution

    An error (ANSI session mode) or failure (Teradata session mode) is reported during procedure execution in any of the following situations:

  • The database objects referenced in the stored procedure have been dropped or renamed after the procedure creation.
  • The attributes of the referenced objects or of the parameters of the objects have been changed.
  • Translation error or failure occurs when the value of an IN or INOUT argument is converted implicitly to the corresponding parameter data type and assigned to the corresponding parameter.
  • See “Dropped, Renamed, or Replaced Objects” on page 321.

    Errors and Failures in Nested Procedures

    If an error or failure occurs in case of nested procedures, the error or failure message text is preceded by the name of the procedure in which the error has occurred.

    Assume that procedure X references another procedure, Y.

     

    IF …

    THEN the error or failure text contains the following text as the procedure name …

    Y returns an error or failure during the execution of procedure X

    Y.

    an error or failure occurs in the calling of procedure X

    X.

    Aborting a CALL Statement

    You can use the standard abort facility provided by the client utility or interface to abort a CALL statement. The following action takes place:

     

    IF you specify the abort request …

    THEN this action is taken…

    during stored procedure execution

    the execution stops.

    The transaction being processed at that time is rolled back, and a failure response is returned.

    after the execution completes

    the response of the CALL request is returned to the client.

    Asynchronous and Synchronous Abort Logic

    An SQL CLI external stored procedure can issue asynchronous aborts. Normally if a client application issues an asynchronous abort, the system aborts the transaction on the Teradata platform, and an abort success message is sent to the client application; however, the system cannot do that for an SQL CLI external stored procedure. The system must roll back the transaction (if there is one) and return the abort status to the SQL CLI external stored procedure without affecting the client application in any way.

    The undesirable side affect is that if the client application had an outstanding transaction, that transaction would no longer exist. As a general rule, you should not write your applications to submit CALL requests inside a transaction unless it is known that the stored procedure will not issue any asynchronous aborts.

    This same issue is true for synchronous abort logic in stored procedures.

    Example : Input Arguments in BTEQ and CLIv2

    Consider the following stored procedure spSample1 that has three IN parameters, p1, p2, and p3 of INTEGER data type. The argument list can be specified in either of the following formats:

         CALL spSample1 (1, 2, 3); 
     
         CALL spSample1(1, CAST(((2 + 4) * 2) AS FORMAT 'ZZZ9'), 3); 

    The rule of specifying the arguments as a positional list applies to all arguments (IN, INOUT, or OUT).

    Example : Input and Output Arguments in BTEQ and CLIv2

    You can use an SQL SHOW PROCEDURE statement to view and verify the parameter types:

         CREATE PROCEDURE spSample2(OUT p1 INTEGER, INOUT p2 INTEGER, 
         IN p3 INTEGER)
          BEGIN
             SET p1 = p3;
             SET p2 = p2 * p3;
          END;

    The call arguments can be specified in any of the following formats:

    Format 1

         CALL spSample2(p1, 20, 3);

    where, the parameter name p1 is supplied as an argument because it is a placeholder for an OUT parameter. The values 20 and 3 are passed as arguments to the INOUT and IN parameters, p2 and p3 respectively.

    The CALL statement returns the following response:

         *** Procedure has been executed..
         *** Total elapsed time was 1 second.
     
                     p1           p2
            -----------  -----------
                      3           60

    Format 2

         CALL spSample2(p1, 20 * 2, 30 + 40); 

    where the expressions (20 * 2) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.

    The CALL statement returns the following response:

         *** Procedure has been executed..
          *** Total elapsed time was 1 second.
         
                  p1           p2
         -----------  -----------
                  70         2800 

    Format 3

         CALL spSample2(CAST(CAST(p1 AS CHARACTER(10)) AS TITLE      'OutputValue'), CAST(20 AS SMALLINT), 30 + 40); 

    where the expressions CAST(20 AS SMALLINT) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.

    The CALL statement returns the following response:

         *** Procedure has been executed..
          *** Total elapsed time was 1 second.
     
         OutputValue      20 
         -----------  ----------
         70               1400 

    Format 4

    This format is used with BTEQ and assumes that data is being imported from a data file.

         USING (a INTEGER) CALL spSample1(p1, :a, 3);

    where 3 is the value passed as an argument to the IN parameter p3. The value read from the input data file is assigned to the variable a and is passed as an argument to the INOUT parameter p2 via a USING clause.

    BTEQ receives the following response from the Teradata platform:

         *** Procedure has been executed..
          *** Total elapsed time was 1 second.
     
                  p1          p2
         -----------  -----------
                   3          30

    Format 5 (Using NAMED and TITLE phrases in the SQL CALL request)

         CALL spSample1(CAST (p1 AS NAMED AA TITLE 'OUT VALUE'), 
         CAST (((20 * 2) + 3) AS TITLE 'INOUT VALUE'), 1);

    The response looks like this:

         *** Procedure has been executed..
          *** Total elapsed time was 1 second.
     
           OUT VALUE  INOUT VALUE
         -----------  ------------
                   1            43

    Example : Stored Procedure or Embedded SQL Input Arguments

    Consider the stored procedure spSample2 defined in Example 2. The arguments can be specified in any of the formats shown:

    Format 1

    Only literals or constant expressions are specified as arguments for the parameters:

  • In a stored procedure:
  •      CALL spSample2(1, 2, 3 + 4); 
       
  • In a C program using embedded SQL:
  •      EXEC SQL CALL spSample2(1, 2, 3 + 4); 

    Format 2

    The application variables contain the values that are passed as arguments:

  • In a stored procedure:
  •      SET AppVar1 = 10;
         SET AppVar2 = 30;
         SET AppVar3 = 40; 
         CALL spSample2(:AppVar1, :AppVar1 + :AppVar2, CAST(:AppVar3 AS
         FORMAT 'Z,ZZ9')); 
  • In a C program using embedded SQL:
  •      AppVar1 = 10;
         AppVar2 = 30;
         AppVar3 = 40; 
         EXEC SQL CALL spSample2(:AppVar1, :AppVar1 + :AppVar2,
                       CAST(:AppVar3 AS FORMAT 'Z,ZZ9')); 

    Format 3

    The combination of the application variables (AppVar1, AppVar2, and AppVar3) and values/expressions are specified as arguments:

  • In a stored procedure:
  •      SET AppVar1 = 10;
         SET AppVar2 = 30;
         SET AppVar3 = 40; 
         CALL spSample2(:AppVar1, 3 + :AppVar2, 3 + 4 + :AppVar3); 
       
  • In a C program using embedded SQL:
  •      AppVar1 = 10;
         AppVar2 = 30;
         AppVar3 = 40; 
         EXEC SQL CALL spSample2(:AppVar1, 3 + :AppVar2, 3 + 4 
                       + :AppVar3); 

    Note that no output parameters are returned from the stored procedure using this format, so the ACTIVITY_COUNT is returned as 0 in the success response.

    Example : Stored Procedure and Embedded SQL Input and Output Arguments

    Consider the stored procedure spSample2. The arguments can be specified in the following format:

    Format 1

  • In a stored procedure:
  •      SET AppVar2 = 30 + AppVar3;
         SET AppVar3 = 40; 
         CALL spSample1(:AppVar1, :AppVar2, :AppVar3); 
     
  • In a C program using embedded SQL:
  •      AppVar2 = 30 + AppVar3;
         AppVar3 = 40; 
         EXEC SQL CALL spSample1(:AppVar1, :AppVar2, :AppVar3); 

    The values specified for AppVar2 and AppVar3 are passed as arguments to p2 and p3, respectively. When the stored procedure execution completes, the output parameter values are returned in AppVar1 and AppVar2. ACTIVITY_COUNT is set to 1.

    Format 2

  • In a stored procedure:
  •      SET AppVar2 = 30 + AppVar3;
         SET AppVar3 = 40; 
         CALL spSample1(:AppVar1, :AppVar2, :AppVar3 + 3); 
     
  • In a C program using embedded SQL:
  •      AppVar2 = 30 + AppVar3;
         AppVar3 = 40; 
         EXEC SQL CALL spSample1(:AppVar1, :AppVar2, :AppVar3 + 3); 

    The values for p2 and p3 are AppVar2 and (3 + AppVar3), respectively. When the stored procedure execution completes, the output parameter values are returned in AppVar1 and AppVar2. ACTIVITY_COUNT is set to 1.

    Example : Preventing Memory Overflow Errors For INOUT Parameters

    If the size of an output value returned to an INOUT parameter is larger than the memory the system had allocated for the input value for that parameter, the CALL request fails and returns an overflow error to the requestor (see “Rules For Specifying Input And Output Parameters” on page 315).

    The following examples illustrate this. Suppose you have created an SQL stored procedure named myintz with an INOUT parameter.

    Example 1

         BTEQ -- Enter your DBC/SQL request or BTEQ command:
         CALL myintz(32767);

    The narrowest data type the system can fit 32,767 into is SMALLINT, so it allocates 2 bytes for the parameter and sets its type as SMALLINT irrespective of the data type assigned to the INOUT parameter when the procedure was created. If this CALL returns a value of 32,768 or more, the system treats it an a memory overflow for a SMALLINT variable and returns an error.

    Example 2

         BTEQ -- Enter your DBC/SQL request or BTEQ command:
         CALL myintz(CAST ('32767' AS INTEGER));

    In this case, the system knows the input value is an INTEGER value because of the explicit casting, so it allocates 4 bytes for the parameter. Then, when the CALL returns 32,768 to the INOUT parameter, the value is within the scope of an INTEGER, the request completes successfully, and no memory allocation error occurs.

         CALL myintz(cast ('32767' AS INTEGER));
     
          *** Procedure has been executed.
          *** Total elapsed time was 42 seconds.
     
             '32767'
         -----------
               32768

    Similarly, if the client program code calling a procedure explicitly defines the data type for the parameter, and the specified data type is capable of containing a larger output value than the default type for the input value, the request completes successfully; otherwise, it does not.

    The following examples illustrate this using a Java external stored procedure named myint2 called from a Java client application. Note that the basic principle applies for any stored procedure, not just external stored procedures. Assume the value returned to the INOUT parameter is 32,768 in both cases.

    Example 3

    Suppose you invoke the Java external stored procedure myint2 as follows:

         stmt.executeUpdate("call myint2(32767);"); 

    The outcome of this call is a memory overflow error, which is identical to that seen in the first example.

    Example 4

    Suppose a Java client program invokes a Java external procedure as follows:

          StringBuffer prepCall = new StringBuffer("CALL myint2(?);");
          System.out.println(prepCall.toString());
     
          CallableStatement cStmt = con.prepareCall(prepCall.toString());
     
          cStmt.setInt(1, integerVar[0]); 
          cStmt.registerOutParameter(1, Types.INTEGER);
     
          // Making a procedure call
          System.out.println("Before executeUpdate()...");
     
          cStmt.executeUpdate();
          System.out.println("Value after executeUpdate(): " 
                             + cStmt.getInt(1)); 
     
          integerVar[0] = cStmt.getInt(1);

    This CALL succeeds because the calling Java program explicitly defined the INOUT parameter as an INTEGER data type, so the system allocates an appropriate amount of memory for an INTEGER input value. As a result, no overflow error occurs when the returned value can be contained within the memory allocated to an INTEGER type.

    Example : Input and Output Arguments in ODBC

    The following example describes the usage of the stored procedures specifying input and output arguments in an ODBC application.

    Consider the stored procedure spSample2 defined as follows:

         CREATE PROCEDURE spSample2(
           OUT p1   INTEGER, 
           INOUT p2 INTEGER, 
           IN p3    INTEGER)
          BEGIN
            SET p1 = p3;
            SET p2 = p2 * p3;
          END;

    The arguments can be specified in the following format:

         SQLBindParameter(..., 1, SQL_PARAM_INPUT_OUTPUT, ..., SQLINTEGER,      ..., ..., AppVar2, sizeof(AppVar2), ...);
         SQLBindParameter(..., 2, SQL_PARAM_INPUT, ..., SQLINTEGER, ...,      ..., AppVar3, sizeof(AppVar3), ...);

    where the second argument in SQLBindParameter() is the question mark number ordered sequentially from left to right, starting at 1.

    Executing the stored procedure:

         {
         constchar *request = "CALL spSample2(p1, ?, ?)"; 
         ...
         ...
         SQLExecDirect(hstmt, request);
         ...
         ...
         }

    Retrieving the output parameter values:

         SQLBindCol(..., 1, ..., AppVar1, ..., ...); 
         SQLBindCol(..., 2, ..., AppVar2, ..., ...); 

    where the second argument in the SQLBindCol() is the parameter number of result data, ordered sequentially left to right, starting at 1.

    Example : Input and Output Arguments in JDBC

    Consider the stored procedure spSample2. It can be executed using the following JDBC API calls:

         CallableStatement cstmt = con.prepareCall("CALL spSample2(p1, ?,      ?)");
         ResultSet rs = cstmt.executeQuery();

    The following alternatives can be used for the second line (ResultSet rs = …):

         boolean bool = cstmt.execute();

    or

         int count = cstmt.executeUpdate();

    The QUESTION MARK character indicates an argument and acts as a placeholder for IN or INOUT parameters in the prepareCall() request. The question mark placeholder arguments must be bound with the application local variables and literals using the CallableStatement.setXXX() JDBC API calls.

    Alternatively, an IN or INOUT argument can be a constant expression.

    The INOUT and OUT arguments need to be registered using the following JDBC API call:

         CallableStatement.registerOutParameter()

    After execution the parameter values can be retrieved from the response using the CallableStatement.getXXX() JDBC API calls.

    Example : Calling a Stored Procedure That Returns Dynamic Result Sets From Embedded SQL

    Consider the following stored procedure that returns a limit of four dynamic result sets to a calling embedded SQL application:

         CREATE PROCEDURE  TESTSP1(INOUT p1 INTEGER, OUT p2 INTEGER)
         DYNAMIC RESULT SETS 4
             BEGIN
              DECLARE tab1_cursor CURSOR WITH RETURN ONLY TO CALLER FOR 
                                  SELECT c1, c2, c3 FROM tab1;
                                  OPEN tab1_cursor;
                                  SET p1 = p1 + 1;
                                  SET p2 = 82;
             END;

    To access the dynamic result set rows returned to the application by this procedure, declare a cursor like the following:

         EXEC SQL BEGIN DECLARE SECTION;
              …       
            long H1;
            long H2;
     
            long M1;
            long M2;
            long M3;
              … 
         EXEC SQL END DECLARE SECTION;
              …
         EXEC SQL
                DECLARE TESTCUR CURSOR
                    FOR 'CALL TESTSP1(:H1, :H2)';
         EXEC SQL
                OPEN TESTCUR;

    If the procedure returns one or more rows, the system sets SQLCODE to 3212 and SQLSTATE to ‘T3212’. This example does not use SQLSTATE return codes. The SQLSTATE codes equivalent to the SQLCODE values in the example are provided for the sake of completeness.

    If the stored procedure returns no rows, the system sets SQLCODE to 0 and SQLSTATE to ‘T0000’:

         if (SQLCODE == 3212) 
               fetch_rows();
         else if (SQLCODE != 0)
               error_check();

    Fetching rows from a cursor declared for a stored procedure is similar to fetching rows from a cursor defined for an SQL request:

         void fetch_rows()
                  do  {
                              EXEC SQL
                                   FETCH TESTCUR INTO :M1, :M2, :M3;
                              …
                      } while (SQLCODE == 0);

    Example : Using an SQL UDF as an Argument for an External Stored Procedure

    This example passes the SQL UDF value_expression as an argument to the external stored procedure spAccount.

         CALL spAccount(test.value_expression(3,4), outp1);

    Example : Specifying a RETURNS or RETURNS STYLE Clause for an OUT Parameter Return Type

    The first example shows how to use a RETURNS clause when invoking an external stored procedure. The RETURNS clause explicitly specifies a return type of INTEGER for the result_1 OUT parameter.

    For this example to be valid, the OUT parameter result_1 must be defined with a data type of TD_ANYTYPE in xsp_1.

         CALL xsp_1(10.25, result_1 RETURNS INTEGER);

    The second example shows how to specify a RETURNS STYLE clause when invoking an external stored procedure.

    This example refers to table t1, which has the following definition.

         CREATE TABLE t1 (
           int_col     INTEGER, 
           varchar_col VARCHAR(40) CHARACTER SET UNICODE);

    The RETURNS STYLE clause implicitly specifies a return type of VARCHAR(40) CHARACTER SET UNICODE for the result_1 OUT parameter of xsp_2 because that is the data type for column varchar_col in table t1.

    For this example to be valid, the OUT parameter result_1 must be defined with a data type of TD_ANYTYPE in xsp_2.

         CALL xsp_2(10, 'abc', result_1 RETURNS STYLE t1.varchar_col);

    Example : Using Both RETURNS and RETURNS STYLE Clauses in the Same Procedure Call

    This example specifies both RETURNS and RETURNS STYLE clauses in a CALL request using the following partial procedure definition.

         CREATE PROCEDURE XSP_1(
           IN A INT, 
           OUT B TD_ANYTYPE, 
           OUT C INT,
           OUT D TD_ANYTYPE, 
           OUT E INT,
           OUT F TD_ANYTYPE)
           …;

    You might call xsp_1 using the following parameter specifications, mixing both RETURNS and RETURNS STYLE OUT parameter return types.

    CALL myXSP1(10, RESULT_B RETURNS INTEGER, RESULT_C, 
                RESULT_D RETURNS STYLE t1.int_col, 
                RESULT_E, RESULT_F RETURNS INTEGER);

    Related Topics

    See SQL Data Definition Language for information on these statements:

  • ALTER PROCEDURE (External Form)
  • ALTER PROCEDURE (SQL Form)
  • CREATE PROCEDURE (External Form)
  • CREATE PROCEDURE (SQL Form)
  • HELP PROCEDURE
  • See SQL Data Types and Literals and SQL External Routine Programming for information about the TD_ANYTYPE data type and how to code external routines to handle it.