Invokes an SQL procedure or external stored procedure.
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: 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: |
|
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. |
CALL is ANSI SQL:2011-compliant.
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.
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).
Interactive SQL, embedded SQL, SQL procedures, and macros.
The following rules apply to executing SQL procedures and external procedures:
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.
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:
If the called procedure has no parameters, you cannot specify any call arguments.
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:
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));
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
|
ACTIVITY_COUNT is set to this value … |
IF the procedure has … |
|
1 |
any output (INOUT or OUT) parameters. |
|
0 |
no output parameters. |
These rules apply to call arguments submitted from applications in BTEQ or CLIv2:
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.
The following additional rules apply to a call argument when the CALL statement is submitted from an ODBC or JDBC application:
A value expression must not contain identifiers prefixed by the COLON character. It must be a constant expression.
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.
The following additional rules apply to a call argument when the CALL statement is submitted from another stored procedure:
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.
The following additional rules apply to a call argument when the CALL statement is submitted from a PP2 embedded SQL application:
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.
OUT_call_variable. The identifier must refer to a host variable.
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.
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 |
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 |
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.
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. |
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.
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:
These conditions also apply to the attributes of any referenced database objects.
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.
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;
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 */;
An error (ANSI session mode) or failure (Teradata session mode) is reported during procedure execution in any of the following situations:
See “Dropped, Renamed, or Replaced Objects” on page 297.
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. |
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. |
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.