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';
|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.
- CONTAINS SQL
- READS SQL DATA
- MODIFIES SQL DATA
For more information and an example, see "Limiting the Types of SQL Statements that a Procedure Executes" in 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.
|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.|