OMBALTER REGISTERED_FUNCTION

Purpose

Alters a function that can be used in a query.

Prerequisites

Should be in the context of a Business Definition Module or use the full path.

Syntax

alterRegisteredFunctionCommand =  ( OMBALTER REGISTERED_FUNCTION 
          "QUOTED_STRING" ( ( "renameClause" [ SET 
          "setPropertiesClauseforRegFun" ] [ SET "setReferenceIconSetClause" ] [
           UNSET "unsetReferenceIconSetClause" ] { "alterFunctionClauses" } ) | 
          ( SET "setPropertiesClauseforRegFun" [ SET "setReferenceIconSetClause"
           ] [ UNSET "unsetReferenceIconSetClause" ] { "alterFunctionClauses" } 
          ) | ( SET "setReferenceIconSetClause" [ UNSET 
          "unsetReferenceIconSetClause" ] { "alterFunctionClauses" } ) | ( UNSET
           "unsetReferenceIconSetClause" { "alterFunctionClauses" } ) | ( 
          "alterFunctionClauses" { "alterFunctionClauses" } ) ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     setPropertiesClauseforRegFun =  PROPERTIES "(" "propertyNameListforRegFun" 
          ")" VALUES "(" "propertyValueList" ")"
     setReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
     unsetReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET
     alterFunctionClauses =  ADD "addFunctionArgClausesForAlter" | MODIFY 
          "modifyFunctionArgClause" | DELETE "deleteFunctionArgClause"
     propertyNameListforRegFun =  ( "UNQUOTED_STRING" | ( PACKAGE | SIGNATURE ) 
          ) { "," ( "UNQUOTED_STRING" | ( PACKAGE | SIGNATURE ) ) }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addFunctionArgClausesForAlter =  PARAMETER "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
     modifyFunctionArgClause =  PARAMETER "QUOTED_STRING" [ "renameClause" ] [ 
          "moveParamToClause" ] [ SET "setPropertiesClause" ]
     deleteFunctionArgClause =  PARAMETER "QUOTED_STRING"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     moveParamToClause =  MOVE TO POSITION "INTEGER_LITERAL"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

alterRegisteredFunctionCommand

This clause alters a function.

QUOTED_STRING

name of the function.

renameClause

Renames a function with a different name.

setPropertiesClauseforRegFun

This clause sets the properties of the object.

setReferenceIconSetClause

Set specified Icon Set.

unsetReferenceIconSetClause

Unset specified Icon Set.

alterFunctionClauses

This clause alters the function parameters.

propertyNameListforRegFun

This is the list of property names.

propertyValueList

This is the list of property values.

addFunctionArgClausesForAlter

This clause adds a function parameter.

QUOTED_STRING

name of the parameter.

modifyFunctionArgClause

This clause modifies a function parameter.

QUOTED_STRING

name of the parameter.

deleteFunctionArgClause

This clause deletes a function parameter.

QUOTED_STRING

name of the parameter.

propertyValue

This is a property value.

setPropertiesClause

This clause sets the properties of the object.

getPropertiesClause

Basic properties for REGISTERED_FUNCTION: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the function 

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the function 

Name: AVAILABLE
Type: Boolean
Valid Values: Y,N
Default: 'N'
Whether the Function is available for the user to use in calculations

Name: RETURN_TYPE
Type: STRING()
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, BLOB, BOOLEAN, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH
NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, RAW, SYS.ANYDATA, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHAR2, XMLTYPE, 
SYS.LCR$_ROW_RECORD, SYS.XMLSEQUENCETYPE, SYS_REFCURSOR
Default: 'NUMBER'
Return type of the function 

Basic properties for PARAMETER:

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the parameter 

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the parameter 

Name: DATATYPE
Type: STRING()
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, BLOB, BOOLEAN, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH
NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, RAW, SYS.ANYDATA, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHAR2, XMLTYPE, 
SYS.LCR$_ROW_RECORD, SYS.XMLSEQUENCETYPE, SYS_REFCURSOR 
Default: 'VARCHAR2'
Datatype of the parameter

moveParamToClause

This clause moves parameters within registered functions.

propertyNameList

This is the list of property names.

REGISTERED_FUNCTION Object

Table 4-52 REGISTERED_FUNCTION Object

Property	Type	Choices	Min	Max	Default	Description
AUTHID	STRING	, CURRENT_USER, DEFINER	none	none	empty string	Generate the transformation with selected AUTHID option. The function will be executed with the permissions defined by the AUTHID clause instead of the function owner's permissions.
CALLED_ON_NULL_INPUT	STRING	, CALLED ON NULL INPUT	none	none	empty string	Specifies that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value.
DB_LOCATION	STRING	none	none	none	empty string	Location for the referenced Function
DEPLOYABLE	BOOLEAN	true, false	none	none	true	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
DETERMINISTIC_STATE	STRING	, DETERMINISTIC, NOT DETERMINISTIC	none	none	empty string	Specifies that the function returns the same result every time, for a given set of inputs.
ENCRYPTION	STRING	, ENCRYPTION	none	none	empty string	Specifies whether the function body will be encrypted when the function is created.
EXECUTE_AS	STRING	, EXECUTE AS CALLER, EXECUTE AS OWNER, EXECUTE AS SELF	none	none	empty string	Generate the transformation with selected EXECUTE AS option. Function will be executed with the permissions defined by the EXECUTE AS clause rather than the function owner's permissions.
EXTERNAL_ACTION	STRING	, EXTERNAL ACTION, NO EXTERNAL ACTION	none	none	empty string	Specifies that the function takes some action that changes the state of an object not managed by the database manager, such as reading or writing a file in the OS-managed folders.
GENERATION_COMMENTS	STRING	none	none	none	empty string	Enter additional comments for the generated code.
INHERIT_ISOLATION_LEVEL	STRING	, INHERIT ISOLATION LEVEL WITH LOCK REQUEST, INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST	none	none	empty string	Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the function inherits the isolation level of the statement that invokes the function. The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.
INHERIT_SPECIAL_REGISTERS	STRING	, INHERIT SPECIAL REGISTERS	none	none	empty string	Specifies that the function will inherit all the special registers from the invoking statement.
IS_DETERMINISTIC	STRING	, DETERMINISTIC	none	none	empty string	Optimization hint that specifies that the function returns the same result every time for a given set of arguments. If the function is called with the same arguments multiple times, then the optimizer may re-use the previous result.
IS_PARALLEL_ENABLE	STRING	, PARALLEL_ENABLE	none	none	empty string	Optimization hint, instructing Oracle to execute the function in parallel whenever called from within a SQL query.
LANGUAGE_SQL	STRING	, LANGUAGE SQL	none	none	empty string	This Optional clause indicates that the function is written in SQL PL. This is the default option, to satisfy SQL99 requirements.
PACKAGE	STRING	none	none	none	empty string	May be used to identify the name of a Package that contains the Function
PARAMETER_CCSID	STRING	, PARAMETER CCSID ASCII, PARAMETER CCSID UNICODE	none	none	empty string	Specifies the encoding scheme to use for all string data passed into and out of the function. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.
PRAGMA_AUTONOMOUS_TRANSACTION	STRING	, PRAGMA AUTONOMOUS_TRANSACTION	none	none	empty string	Instructs the PL/SQL compiler to mark the function as independent, which allows the function to suspend the main transaction.
SCHEMABINDING	STRING	, SCHEMABINDING	none	none	empty string	Specifies that the function is bound to the database objects that it references (that is, they cannot be dropped or modified). This condition will prevent changes to the function if other schema-bound objects are referencing it. If a function is created with SCHEMABINDING, then the function is a deterministic function.
SPECIFIC	STRING	none	none	none	empty string	Uniquely identifies a function within the current schema. The specific name can be the same as the function name, and it can be up to 18 characters long. If the SPECIFIC keyword is omitted, then the IBM DB2 UDB Database Manager automatically generates a unique identifier for the function in the format SQLyymmddhhmmssxxx.
SQL_DATA	STRING	, CONTAINS SQL, MODIFIES SQL DATA, READS SQL DATA	none	none	empty string	Specifies what type of SQL statements can be executed by the function. CONTAINS SQL specifies that the function can only execute SQL statements that do not read or modify SQL data. READS SQL DATA specifies that only SQL statements that do not modify SQL data can be executed by the function. MODIFIES SQL DATA specifies that all SQL statements supported in dynamic-compound-statement can be executed by the function.
STATIC_DISPATCH	STRING	, STATIC DISPATCH	none	none	empty string	Specifies that the function is to return the static values of an ARRAY or a user-defined type. Required if you have a non-SQL function that uses an ARRAY or a user-defined type as parameters.
WITH_NULL_INPUT	STRING	, CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT	none	none	empty string	Specifies that if RETURNS NULL ON NULL INPUT is specified in a CLR function, then SQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. If CALLED ON NULL INPUT is specified, then the function body executes even if NULL is passed as an argument.

OMBALTER REGISTERED_FUNCTION

Purpose

Prerequisites

Syntax

Parameters

alterRegisteredFunctionCommand

QUOTED_STRING

renameClause

setPropertiesClauseforRegFun

setReferenceIconSetClause

unsetReferenceIconSetClause

alterFunctionClauses

propertyNameListforRegFun

propertyValueList

addFunctionArgClausesForAlter

QUOTED_STRING

modifyFunctionArgClause

QUOTED_STRING

deleteFunctionArgClause

QUOTED_STRING

propertyValue

setPropertiesClause

getPropertiesClause

moveParamToClause

propertyNameList

REGISTERED_FUNCTION Object

See Also