CREATE PROCEDURE and REPLACE PROCEDURE Syntax Elements (External Form) - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
database_name
An optional database name specified if the procedure is to be created or replaced in a non-default database.
If you do not specify a database name, Vantage creates or replaces the procedure within the current database.
user_name
An optional user name specified if the procedure is to be created or replaced in a non-default user.
If you do not specify a user name, Vantage creates or replaces the procedure within the current user.
procedure_name
The calling name for the external procedure.
If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. If you attempt to do so, Vantage aborts the request and returns an error to the requestor. Instead, use a multibyte session character set.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
A procedure name is mandatory for all external procedures.
procedure_name must match the spelling and case of the C, C++, or Java procedure name exactly if you do not specify an external_procedure_name. This applies only to the definition of the procedure, not to its use.
number_of_sets
Specifies that number_of_sets of dynamic result sets can be returned.
The range of valid values for number_of_sets is 0 through 15, inclusive.
external_procedure_name
The entry point for the procedure object.
The name for an external procedure must conform to object naming rules. See Teradata Vantage™ - SQL Fundamentals, B035-1141.
Case is significant and must match the C or C++ procedure name.
This option is not valid for Java external procedures. Instead, you must specify the EXTERNAL NAME external_Java_reference_string option.
EXTERNAL SECURITY
This clause is recommended for procedures that perform operating system I/O operations because it permits you to specify a particular OS user under whom the function runs. Otherwise, a protected mode procedure runs under the generic user tdatuser.
See also CREATE AUTHORIZATION and REPLACE AUTHORIZATION.
DEFINER
The UDF runs in the client user context of the associated security authorization object created for this purpose, which is contained within the same database as the procedure.
  • If you specify an authorization name, you must define an authorization object with that name before you can invoke the procedure.
  • If you do not specify an authorization name, you must define a default DEFINER authorization object.
The default authorization object must be defined before a user can run the procedure.
Vantage reports a warning if the specified authorization name does not exist at the time the procedure is created, stating that no authorization name exists.
If you then attempt to execute the procedure, the request aborts and the system returns an error to the requestor.
authorization_name
An optional authorization name for this DEFINER as defined by CREATE AUTHORIZATION.
INVOKER
The procedure runs using the INVOKER authorization associated with the logged on user who is running the function.