COMPILE Command | Basic Teradata Query - 17.00 - COMPILE - Basic Teradata Query

Basic Teradata® Query Reference

Basic Teradata Query
June 2020
Programming Reference


Defines (creates or replaces) an SQL stored procedure in the database using an SPL input file.


COMPILE syntax (workstation-attached systems)

COMPILE syntax (mainframe-attached systems)

where the following is true:

Name of a file containing source text for creating SQL stored procedures on workstation-attached systems, or a DD name on mainframe-attached systems.

For workstation-attached systems, if the name of the file includes a comma, semicolon, or space character, enclose the entire file name in single or double quotation marks.

In the following example, myproc.spl is the file name:

.COMPILE FILE 'myproc.spl'

Also, when text follows a file name, that file name must be enclosed in quotation marks. For example:

Specifies that the SQL stored procedure source text is not to be stored in the database.
Specifies that the SQL stored procedure source text is to be stored in the database. If no argument is specified in the “With” part of the command, BTEQ sets the source text storage option to SPL.
Required syntax to specify the SPL storing option.
Name of the z/OS JCL DD statement that defines the file from which SPL statements are read on mainframe-attached systems.
Specify the dot (.) at the beginning, or the semicolon (;) at the end, or both, in the COMPILE command. If the dot character is missing, BTEQ generates a warning message but executes the command.

Usage Notes

The COMPILE command is used only for SQL (internal) stored procedures. For differences between external and SQL stored procedures, see Creating and Using Stored Procedures.

CREATE PROCEDURE privilege is required for access to the database specified in the SQL stored procedure. For replacing or modifying an existing SQL stored procedure using the REPLACE PROCEDURE statement in the SQL stored procedure source text, DROP privilege are required on the particular SQL stored procedure or DROP PROCEDURE privilege on the database containing it.

The SQL stored procedure definition in the input file begins with the keywords CREATE or REPLACE PROCEDURE, followed by the SQL stored procedure body, and ends with a semicolon. The definition can also contain parameters, local variables, exception handlers and comments.

The following rules apply:
  1. Log on to a database user is required before executing this command.
  2. The source text for an SQL stored procedure must be written in an input file. Do not issue the CREATE or REPLACE PROCEDURESQL statement at the BTEQ prompt.
  3. One file can contain source text for only one SQL stored procedure. It cannot contain any other BTEQ commands.
  4. The COMPILE command must be the last statement in an open transaction for an SQL stored procedure.

If there are no compilation errors, the entire SQL stored procedure source text is stored in the specified database. It can be retrieved later using the SHOW PROCEDURE SQL statement.

For other details of the features and limitations of SPL, plus sample input files, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

For Unicode sessions, a BOM is optional at the beginning of a UTF-8 or UTF-16 file, and the encoding of an SQL stored procedure file must match the encoding of the session character set.

SPL Attributes

Use these attributes to specify whether to store or not to store the SQL stored procedure source text.

Select SPL to store the SQL stored procedure source text in the database. This is the default option.

NOSPL specifies that the SQL stored procedure source text is not to be stored in the database. For such SQL stored procedures, the SHOW PROCEDURE SQL statement returns an error or failure.

Session Mode

The session mode (Teradata or ANSI) is stored with each SQL stored procedure created in the database. This information is used during SQL stored procedure execution.

An SQL stored procedure created in a given mode can be executed only in the same mode. For example, an SQL stored procedure created in ANSI mode can be executed only in ANSI mode.

This rule enables a definition of an SQL stored procedure to be included with the transaction control statements that are specific to a given session mode, and to define defaults associated with a session mode.

Using the = Command to Repeat a COMPILE Request

Use the = command to repeat a COMPILE command request one time. The = command saves time and requires fewer keystrokes to initiate this functionality.

Unqualified Objects in the Source Text

If any database objects referenced in the SQL statements inside the SQL stored procedure source text are not qualified with a database name, it is assumed that the objects are in the current default database of the session which created the SQL stored procedure.

The unqualified objects are qualified before creating the SQL stored procedure database object.

COMPILE Command Errors

The COMPILE command results in a SUCCESS, OK, or FAILURE response from the database.

If a failure is reported (in either ANSI or Teradata mode) as a response to the COMPILE command, the entire transaction is rolled back.

Compilation Errors

SPL compilation errors and warnings are reported as part of a SUCCESS or OK response. They have no impact on an open transaction.

If there are compilation errors, the SQL stored procedure is not created or replaced.

If only compilation warnings occur, the SQL stored procedure is created or replaced as requested.

See Error Handling for details of the error response size and organization.

Refer to Teradata Vantage™ - Database Messages, B035-1096 for a complete list of the SPL compilation error and warning messages.

Aborting a Compilation

When aborting a request using the ABORT feature, the following rules apply:
  • For CREATE PROCEDURE, the SQL stored procedure is not defined in the database.
  • For REPLACE PROCEDURE, the original SQL stored procedure definition is retained.

Compatibility Across Platforms

An SQL stored procedure executable is created on the database server. The executable code is compatible only with the operating environment of the server.

Example 1 – COMPILE

This example shows the correct usage of the COMPILE command:

.LOGON server/user, password
SELECT date;

Example 2 – COMPILE

This example requires a COMMIT/ROLLBACK/ABORT after the COMPILE command to close the transaction.

LOGON server/user, password;
SELECT date;
.COMPILE FILE spSample1;

Example 3 – COMPILE

This example is not valid because the COMPILE command is not the last request in an open transaction (it is followed by a SELECT SQL statement):

LOGON server/user, password;
SELECT date;