create function (SQLJ)

Creates a user-defined function by adding a SQL wrapper to a Java static method. Can return a value defined by the method.

Syntax

create function [or replace] [owner.]sql_function_name
		 ([ sql_parameter_name sql_datatype 
			[(length)| (precision[, scale ])]
		[[, sql_parameter_name sql_datatype 
			[(length)| (precision[, scale])]]
		...]])
	returns sql_datatype 
		[(length)| (precision[, scale])]
	[modifies sql data] 
	[returns null on null input | 
		called on null input]
	[deterministic | not deterministic]
	[exportable]
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[, java_datatype 
		...]])] '

Parameters

Examples

Usage

  • You cannot create a SQLJ function with the same name as an SAP ASE built-in function.

  • You can create user-defined functions (based on Java static methods) and SQLJ functions with the same class and method names.

    Note: The SAP ASE server searching order ensures that the SQLJ function is always found first.
  • You can include a maximum of 31 parameters in a create function statement.

  • When a function is created, the SAP ASE server checks to see if it is a SQL user-defined function or a SQLJ user-defined function. If it is the latter, the SAP ASE server checks for “sa” permissions. If it is a SQL function the SAP ASE server checks for create function privileges.

  • If the replaced SQLJ function is called by another function, both functions will be recompiled when called.

  • If the interface of the replaced function does not match that in the calling function, then the calling function must be replaced, otherwise the calling function raises an error. You can execute sp_depends on the replaced function to check for any calling objects.

  • If a function is referenced in a computed column or functional index, it cannot be replaced.

For objects dependent on replaced functions:

See also:
  • See Java in Adaptive Server Enterprise for more information about create function.

  • sp_depends, sp_help, sp_helpjava, sp_helprotect in Reference Manual: Procedures

Permissions

Any user who impersonates the function owner through an alias or setuser cannot replace the function.

The permission checks for create function (SQLJ) differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must have the create function privilege to create a function (SQLJ). To create a function (SQLJ) for another user, you must have the create any function privilege.

You must be the function owner to replace the function.

Disabled

With granular permissions disabled, you must be the database owner, a user with sa_role, or have the create function privilege to create a function (SQLJ). To create a function (SQLJ) for another user, you must have sa_role.

You must be the function owner to replace the function.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

97

Audit option

install

Command or access audited

create function

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if a set proxy is in effect

  • or replace – for create or replace
Related reference
create procedure
create function (SQLJ)
drop function (SQLJ)