CREATE PROCEDURE Statement (Java UDF)

Creates an interface to an external Java table UDF.

CREATE PROCEDURE Statement reference information for external procedures is located in a separate topic. CREATE PROCEDURE Statement reference information for table UDFs is located in a separate topic.

If your query references SAP Sybase IQ tables, note that different syntax and parameters apply compared to a query that references only catalog store tables.

Java table UDFs are only supported in the FROM clause.

Syntax

For a query referencing at least one SAP Sybase IQ table:

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[ RESULT (result-column, ...)]
[ SQL SECURITY { INVOKER | DEFINER } ] 
EXTERNAL NAME ‘java-call’  [ LANGUAGE Java ] }

For a query referencing catalog store tables only:

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[  RESULT (result-column, ...)]
| NO RESULT SET
[ DYNAMIC RESULT SETS integer-expression ]
[ SQL SECURITY { INVOKER | DEFINER } ]  
EXTERNAL NAME ‘java-call’ [ LANGUAGE Java ] }

Parameters

parameter – For a query referencing at least one SAP Sybase IQ table:
[ IN parameter_mode parameter-name data-type [ DEFAULT expression ]

For a query referencing catalog store tables only:

[ IN | OUT | INOUT ] parameter_mode parameter-name data-type [ DEFAULT expression ]
result-column – column-name data-type
JAVA – [ ALLOW | DISALLOW SERVER SIDE REQUESTS ]
java-call – '[ package-name.]class-name.method-name method-signature'

Usage

For Java table functions, exactly one result set is allowed. If the Java table functions are joined with an SAP Sybase IQ table or if a column from an SAP Sybase IQ table is an argument to the Java table function then only one result set is supported.

If the Java table function is the only item in the FROM clause then N number of result sets are allowed.

JAVA: [ ALLOW | DISALLOW SERVER SIDE REQUESTS ]:

DISALLOW is the default.

ALLOW indicates that server-side connections are allowed.

Note: Do not specify ALLOW unless necessary. A setting of ALLOW slows down certain types of SAP Sybase IQ table joins. If you change a procedure definition from ALLOW to DISALLOW, or vice-versa, the change will not be recognized until you make a new connection.

Do not use UDFs with both ALLOW SERVER SIDE REQUESTS and DISALLOW SERVER SIDE REQUESTS in the same query.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—The Transact-SQL CREATE PROCEDURE statement is different.
SQLJ—The syntax extensions for Java result sets are as specified in the proposed SQLJ1 standard.

Permissions

Unless creating a temporary procedure, a user must have the CREATE PROCEDURE system privilege to create a procedure for themselves. To create UDF procedure for others, a user must specify an owner and have either the CREATE ANY PROCEDURES or CREATE ANY OBJECT system privilege. If a procedure has an external reference, a user must also have the CREATE EXTERNAL REFERENCE system privilege, in addition to the previously mentioned system privileges, regardless of whether or not they are the owner of procedure.