Defines (creates or replaces) an SQL stored procedure in the database using an SPL input file.
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:
.COMPILE FILE 'myproc.spl' WITH NOSPL
- 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.
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.
- Log on to a database user is required before executing this command.
- 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.
- One file can contain source text for only one SQL stored procedure. It cannot contain any other BTEQ commands.
- 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.
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.
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.
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
- 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:
.SET SESSION TRANS BTET .LOGON server/user, password BT; SELECT date; .COMPILE FILE spSample1 ET;
Example 2 – COMPILE
This example requires a COMMIT/ROLLBACK/ABORT after the COMPILE command to close the transaction.
SET SESSION TRANS ANSI; LOGON server/user, password; SELECT date; .COMPILE FILE spSample1; COMMIT;
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):
SET SESSION TRANS BTET; LOGON server/user, password; BT; COMPILE FILE spSample1; SELECT date; ET;