External Stored Procedures that Execute SQL - Analytics Database - Teradata Vantage

Here is a synopsis of the steps you take to develop, compile, install, and invoke a C or C++ external stored procedure that uses CLIv2 to execute SQL:

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

   You can use the Teradata C/C++ UDF Debugger, which is a version of GDB (the gnu Source-Level Debugger) that contains extensions for the database. For more information, see C/C++ Command-line Debugging for UDFs.

2. If the external stored procedure requires access to specific operating system resources, use CREATE AUTHORIZATION or REPLACE AUTHORIZATION to create a context that identifies a native operating system user and allows external stored procedures to perform I/O by running as separate processes under the authorization of that user.
3. Use CREATE PROCEDURE or REPLACE PROCEDURE with options that provide specific information about the external stored procedure.

   Option	Description
   NO SQL (Default) CONTAINS SQL READS SQL DATA MODIFIES SQL DATA	Indicates whether the external stored procedure can execute SQL statements and whether those statements can read or modify SQL data in the database.
   LANGUAGE C LANGUAGE CPP	Identifies the source code language of the external stored procedure.
   PARAMETER STYLE SQL (Default) PARAMETER STYLE TD_GENERAL	Indicates whether the external stored procedure can accept null IN or INOUT arguments or return null IN or INOUT arguments.
   DYNAMIC RESULT SETS (Optional)	Specifies the number of result sets that the external stored procedure returns.
   EXTERNAL NAME	Provides the location of the source code and specifies the Teradata CLI package name.
   EXTERNAL SECURITY (Optional)	Associates execution of the external stored procedure with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement in the previous step.

   Recommendation: In general, you should not create external stored procedures in Teradata system databases such as SYSLIB or SYSUDTLIB. For details, see Installing a C/C++ External Stored Procedure.

   The external stored procedure is compiled, linked to a CLI-specific external stored procedure dynamic linked library (DLL or SO) associated with the database in which the external stored procedure resides, and distributed to all database nodes in the system.

4. Test the external stored procedure until you are satisfied it works correctly.
5. Use GRANT to grant privileges to users who are authorized to use the external stored procedure.