Types of Clauses Supported by CREATE/REPLACE PROCEDURE | Vantage - 17.10 - SQL DATA ACCESS Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The SQL Data Access clause indicates whether a procedure can issue SQL statements and, if so, what type. An external procedure that contains “SQL statements” actually contains CLIv2, ODBC, or JDBC calls for those statements, not the standard SQL statements themselves.

You can specify the SQL DATA ACCESS and LANGUAGE clauses in any order.

By supporting the execution of SQL calls from external procedures, the following things can all be done:
  • You can write external procedures to perform SQL requests using CLIv2 or JDBC in the same way a client application can make CLIv2 calls to perform SQL requests.
  • You can use the CREATE PROCEDURE (External Form) and REPLACE PROCEDURE (External Form) statements to specify a data access clause that defines the specific capabilities of SQL calls made from the external procedure.

When a CLIv2 external procedure specifies the MODIFIES SQL DATA option for its data access clause, for example, the system knows that the procedure must be linked with an API, but it does not know which API the procedure must be linked with. To handle this, the EXTERNAL NAME clause must explicitly specify that the procedure is to link with the CLIv2 libraries.

Explicitly specifying a package to link with in the EXTERNAL NAME clause is not valid for external procedures that use JDBC. For those procedures, the determination is made based on the combination of the LANGUAGE and SQL Data Access clauses you specify for the CREATE PROCEDURE or REPLACE PROCEDURE request.

Once it knows this information, the system takes care of finding the proper libraries, linking with them, and setting up the proper environmental variables during execution.

To avoid link and execution conflicts with existing UDF libraries, the database maintains a dynamic library that is separate from the standard UDF library for each database that contains CLIv2- or Java-based external procedures.