../E14406-01.epub /> ../E14406-01.mobi />

OMBCREATE TABLE_FUNCTION

Purpose

To create a Table Function.

Prerequisites

Should be in the context of Oracle Module or Package. The REFCursorType and PLSQLTableType which are set as Datatype for parameters should pre-exist.

Syntax

createTableFunctionCommand =  OMBCREATE ( TABLE_FUNCTION "QUOTED_STRING" 
          SET "setPropertiesClause" [ SET "setReferenceIconSetClause" ] { ADD 
          "addFuncProcParameterClause" } )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
     addFuncProcParameterClause =  PARAMETER "QUOTED_STRING" [ SET 
          "setPropertiesClause" ]
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )

Parameters

createTableFunctionCommand

Creates a table function

setPropertiesClause

Sets properties (core, logical, physical, user-defined) for Table Function and its Parameters. Valid properties are shown below:

setPropertiesClause

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

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

Name: PARALLEL_EXECUTION
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: Empty string
Enables Parallel Execution of the Table Function

Name: PIPELINED_EXECUTION
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: Empty string
Enables Partitioned Execution of the Table Function

Name: ORDER_METHOD
Type: STRING(9)
Valid Values: ORDERBY, CLUSTERBY
Default: ORDERBY
Order Method for the Table Function

Name: PARTITION_METHOD
Type: STRING(5)
Valid Values: NONE, ANY, HASH, RANGE
Default: NONE
Partition Method for the Table Function

Name: RETURN_TYPE
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Name of the Return Type of this Table Function. For this release, this has to be a PLSQL Table Type whose datatype has to be PLSQLRecordType. Also, it should already be defined in USER_TYPES Package in this Module. 

Name: IMPLEMENTATION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Implementation code for this Table Function.

Name: IS_DETERMINISTIC
Type: STRING(5)
Valid Values: TRUE, FALSE
Default: Empty string
Whether this Table Function is Deterministic.

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(4000)
Valid Values: Any valid REF cursor type
Default: Empty string
Datatype of the Parameter. Parameter will always be IN type for Table Function. For this release, it has to be a REF Cursor type. Also, this REF Cursor should already be defined in USER_TYPES Package in this Module.

setReferenceIconSetClause

Set reference icon set to this table function.

addFuncProcParameterClause

Adds a Parameter with the given name and datatype to the Table Function. The datatype of the Parameter is set as its DATATYPE property.

propertyNameList

Comma separated list of property names. Property names are unquoted.

propertyValueList

Comma separated list of property values.

propertyValue

Value of a property.

TABLE_FUNCTION Object

Table 9-107 TABLE_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.

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.

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.