15.00 - External Stored Procedures - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

External Stored Procedures

External stored procedures are written in the C, C++, or Java programming language, installed on the database, and then executed like stored procedures.

Usage

Here is a synopsis of the steps you take to develop, compile, install, and use external stored procedures:

1 Write, test, and debug the C, C++, or Java code for the procedure.

2 If you are using Java, place the class or classes for the external stored procedure in an archive file (JAR or ZIP) and call the SQLJ.INSTALL_JAR external stored procedure to register the archive file with the database.

3 Use CREATE PROCEDURE or REPLACE PROCEDURE for external stored procedures to create a database object for the external stored procedure.

4 Use GRANT to grant privileges to users who are authorized to use the external stored procedure.

5 Invoke the procedure using the CALL statement.

Executing SQL From External Stored Procedures

To execute SQL, a C or C++ external stored procedure can use CLIv2 and a Java external stored procedure can use JDBC.

A C or C++ external stored procedure can also call the FNC_CallSP library function to call a stored procedure that contains SQL.

Differences Between Stored Procedures and External Stored Procedures

Using external stored procedures is very similar to using stored procedures, except for the following:

  • Invoking an external stored procedure from a client application does not affect the nesting limit for stored procedures.
  • To install an external stored procedure on a database, you must have the CREATE EXTERNAL PROCEDURE privilege on the database.
  • The CREATE PROCEDURE statement for external stored procedures is different from the CREATE PROCEDURE statement for stored procedures. In addition to syntax differences, you do not have to use the COMPILE command in BTEQ.
  • Related Topics

     

    For details on …

    See …

    external stored procedure programming

    SQL External Routine Programming.

    invoking external stored procedures

    the CALL statement in SQL Data Manipulation Language.

    installing external stored procedures on the server

    the CREATE/REPLACE PROCEDURE statement in SQL Data Definition Language.

    archiving and restoring external stored procedures

    Teradata Archive/Recovery Utility Reference.