Usage Notes - Teradata Database

Invocation

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

CALL Statements and Multistatement or Iterated Requests

You cannot include CALL statements in iterated requests or multistatement requests.

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: Preventing Memory Overflow Errors For INOUT Parameters.

    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
  • Rules For Calling Procedures From Embedded SQL
  • Rules For Call Arguments In ODBC And JDBC
  • Rules For Call Arguments In Nested Procedures
• 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:

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

Rules For Calling Procedures From Embedded SQL

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:

• 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:

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

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:

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:

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.

These conditions also apply to the attributes of any referenced database objects.

Java External Procedure-Specific Behavior

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 1

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

Scenario 2

      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 3

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

See Dropped, Renamed, or Replaced Objects.

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.

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:

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.