Defining the SQL External Stored Procedure | Teradata Vantage - Defining the SQL External Stored Procedure - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
rin1593638965306.ditamap
dita:ditavalPath
rin1593638965306.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

After you register the JAR file containing the class or classes that implement the external stored procedure, you can define the SQL external stored procedure with the CREATE PROCEDURE or REPLACE PROCEDURE statement.

Database for the External Stored Procedure

When you execute the CREATE PROCEDURE or REPLACE PROCEDURE statement to define an external stored procedure that is implemented by a class in the JAR file, you must define it in the same database in which the JAR file was registered.

Specifying the Class Name, Method Name, and JAR Identifier

The CREATE PROCEDURE and REPLACE PROCEDURE statements provide the EXTERNAL NAME clause for specifying the identifier of the registered JAR file, and the class and method name in the JAR file that implements the external stored procedure.

Consider the following CREATE PROCEDURE statement:

CREATE PROCEDURE GetReport(INOUT Str VARCHAR(120))
   LANGUAGE JAVA
   READS SQL DATA
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'Report_JAR:report.getReport';

where:

THIS part of the EXTERNAL NAME clause … Specifies the …
Report_JAR identifier of the registered JAR file that was provided to the SQLJ.INSTALL_JAR procedure.
report name of the class in the registered JAR file that implements the Java external stored procedure.
getReport method name in the specified class that the database invokes when the external stored procedure is called in a CALL statement.

Specifying the Type of Data Access

If the external stored procedure does not use JDBC to execute SQL, the CREATE PROCEDURE or REPLACE PROCEDURE statement can specify the NO SQL data access clause to indicate that the external stored procedure does not execute SQL.

If the external stored procedure executes SQL, the CREATE PROCEDURE or REPLACE PROCEDURE statement must specify one of the following data access clauses:
  • CONTAINS SQL
  • READS SQL DATA
  • MODIFIES SQL DATA

For more information and an example, see Executing SQL in Java External Stored Procedures.

Default and Temporary Paths

To manage external stored procedures, Teradata uses default and temporary paths for external stored procedure creation and execution, including a temporary directory where external stored procedures are compiled.

For information, including the names of external stored procedure default and temporary paths, see Teradata Vantage™ - Database Administration, B035-1093.

Related Information

FOR more information on … SEE …
the CREATE PROCEDURE and REPLACE PROCEDURE statements Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
the privileges that apply to external stored procedures Teradata Vantage™ - Database Administration, B035-1093.