16.20 - Installing External Stored Procedures That Use CLIv2 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
The CREATE PROCEDURE or REPLACE PROCEDURE statement that you use to install an external stored procedure that uses CLIv2 must specify the following options:
  • An EXTERNAL NAME clause that includes the Teradata CLI package name
  • One of the following options that identify whether the SQL statements that the external stored procedure executes read or modify SQL data in the database:
    • CONTAINS SQL
    • READS SQL DATA
    • MODIFIES SQL DATA

Here is an example

REPLACE PROCEDURE ET001_xsp1(IN A_Name VARCHAR(10),
                             OUT resultx VARCHAR(16000))
   LANGUAGE C
   MODIFIES SQL DATA
   PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'SP!CLI!CS!ET001_xsp1!ET001_xsp.c';

If the external stored procedure returns dynamic result sets to the client application or the caller of the external stored procedure, the CREATE PROCEDURE or REPLACE PROCEDURE statement must also specify the DYNAMIC RESULT SETS option.

Here is an example:

REPLACE PROCEDURE ET001_xsp1(IN A_Name VARCHAR(10),
                             OUT resultx VARCHAR(16000))
   LANGUAGE C
   READS SQL DATA
   PARAMETER STYLE TD_GENERAL
   DYNAMIC RESULT SETS 2
EXTERNAL NAME 'SP!CLI!CS!ET001_xsp1!ET001_xsp.c';