CREATE PROCEDURE Statement (Java UDF)

For CREATE PROCEDURE reference information for external procedures, see CREATE PROCEDURE Statement (External Procedures). For CREATE PROCEDURE reference information for table UDFs, see CREATE PROCEDURE Statement (Table UDF)

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1 – For a query referencing at least one SAP IQ table:

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

Syntax 2 – 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 ] }


parameter - (back to Syntax 1) or (back to Syntax 2)
   [ IN <parameter_mode> <parameter-name> <data-type> 
   [ DEFAULT <expression> ] 

result-column - (back to Syntax 1) or (back to Syntax 2)
   <column-name> <data-type>

java-call - (back to Syntax 1) or (back to Syntax 2)
   <'[ package-name.]class-name.method-name method-signature'>

java - (back to Syntax 1) or (back to Syntax 2)
   [ ALLOW | DISALLOW SERVER SIDE REQUESTS ]

Parameters

(back to top)

java 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 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.

Usage

(back to top)

If your query references SAP 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.

For Java table functions, exactly one result set is allowed. If the Java table functions are joined with an SAP IQ table or if a column from an SAP 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.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Database products—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

(back to top)

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.