The CREATE FUNCTION (OLE DB External Table) statement is used to register a user-defined OLE DB external table function to access data from an OLE DB provider.
A table function can be used in the FROM clause of a SELECT.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Group privileges are not considered for any table or view specified in the CREATE FUNCTION statement.
If the SECURED option is specified, the authorization ID of the statement must include SECADM authority or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).
>>-CREATE FUNCTION--function-name-------------------------------> >--(--| parameter-declaration |--)--●---------------------------> .-,---------------------------. V | >--RETURNS TABLE--(----column-name--| data-type2 |-+--)---------> >--| option-list |--------------------------------------------->< parameter-declaration |--+----------------+--| data-type1 |--+--------------------+---| '-parameter-name-' '-| default-clause |-' data-type1, data-type2 |--+-| built-in-type |----+-------------------------------------| +-distinct-type-name---+ +-structured-type-name-+ '-REF--(--type-name--)-' built-in-type |--+-+-SMALLINT----+----------------------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+-------------------------------------------------------------------+ | '-(16)-' | | .-(1)------------------------. | +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+ | | | '-CHAR------' '-(integer-+-------------+-)-' | | (1) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)-------. | +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+ | | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | | | | | '-CHARACTER-' | | | | | '-+-NVARCHAR-------------------------+--(integer)-' | | | | +-NCHAR VARYING--------------------+ | | | | '-NATIONAL--+-CHAR------+--VARYING-' | | | | '-CHARACTER-' | | | | .-(1M)-------------. | | | '-+-NCLOB---------------------------+--+------------------+-' | | +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' | | '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+----------------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+-----------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | | .-SYSPROC.-. (2) (3) | '-+----------+--DB2SECURITYLABEL-------------------------------------------------------' default-clause |--DEFAULT--+-NULL-------------+--------------------------------| +-constant---------+ +-special-register-+ +-global-variable--+ '-(--expression--)-' option-list |--●--LANGUAGE----OLEDB----●--+-------------------------+--●----> '-SPECIFIC--specific-name-' .-NOT DETERMINISTIC-. >--EXTERNAL--NAME--'string'--●--+-------------------+--●--------> '-DETERMINISTIC-----' .-STATIC DISPATCH-. .-RETURNS NULL ON NULL INPUT-. >--+-----------------+--●--+----------------------------+--●----> '-CALLED ON NULL INPUT-------' .-NO EXTERNAL ACTION-. >--+--------------------+--●--+----------------------+--●-------> '-EXTERNAL ACTION----' '-CARDINALITY--integer-' .-NOT SECURED-. >--+-------------+----------------------------------------------| '-SECURED-----'
The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.
If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939).
A number of names used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.
The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.
CREATE FUNCTION WOOFER() ...
No two identically-named functions within a schema are permitted to have exactly the same type for all corresponding parameters. Lengths, precisions, and scales are not considered in this type comparison. Therefore, CHAR(8) and CHAR(35) are considered to be the same type. A weakly typed distinct type specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. A duplicate signature returns an error (SQLSTATE 42723).
For a more complete description of each built-in data type, see "CREATE TABLE".
For a user-defined distinct type, the length, precision, or scale attributes for the parameter are those of the source type of the distinct type (those specified on CREATE TYPE). A distinct type parameter is passed as the source type of the distinct type. If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.
The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.
The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).
A default cannot be specified for a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB).
The specific-name may be the same as an existing function-name.
If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error (SQLSTATE 42882) is raised.
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.
The 'string' option is a string constant with a maximum of 254 bytes.
The string specified is used to establish a connection and session with an OLE DB provider, and retrieve data from a rowset. The OLE DB provider and data source do not need to exist when the CREATE FUNCTION statement is executed.
>>-'--+-server--!--+--------+-----------------------------------------------+--'->< | '-rowset-' | '-!--+--------+--!--connectstring--+--------------------------------+-' '-rowset-' '-!--COLLATING_SEQUENCE = -+-N-+-' '-Y-'
If server is provided, connectstring or COLLATING_SEQUENCE are not allowed in the external name. They are defined as server options CONNECTSTRING and COLLATING_SEQUENCE. If no server is provided, a connectstring must be provided. If rowset is not provided, the table function must have an input parameter to pass through command text to the OLE DB provider.
LANGUAGE OLEDB table functions can be created on any platform, but only executed on platforms supported by Microsoft OLE DB.
If RETURNS NULL ON NULL INPUT is specified and if at execution time any one of the function's arguments is null, the user-defined function is not called and the result is the empty table; that is, a table with no rows.
If CALLED ON NULL INPUT is specified, then at execution time regardless of whether any arguments are null, the user-defined function is called. It can return an empty table or not, depending on its logic. But responsibility for testing for null argument values lies with the UDF.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility. Similarly, NOT NULL CALL may be used as a synonym for RETURNS NULL ON NULL INPUT.
If the CARDINALITY clause is not specified for a table function, a finite value is assumed as the default. The finite value is the same value that is assumed for tables for which the RUNSTATS utility has not gathered statistics.
Warning: If a function does, in fact, have infinite cardinality - that is, it returns a row every time it is called to do so, and never returns the "end-of-table" condition - then queries that require the end-of-table condition to correctly function will be infinite, and will have to be interrupted. Examples of such queries are those that contain a GROUP BY or an ORDER BY clause. Writing such UDFs is not recommended.
The SECURED attribute is considered to be an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. The database manager assumes that such a control audit procedure is in place for all subsequent ALTER FUNCTION statements or changes to external packages.
CREATE FUNCTION orders ()
RETURNS TABLE (orderid INTEGER,
customerid CHAR(5),
employeeid INTEGER,
orderdate TIMESTAMP,
requireddate TIMESTAMP,
shippeddate TIMESTAMP,
shipvia INTEGER,
freight dec(19,4))
LANGUAGE OLEDB
EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
Data Source=c:\sqllib\samples\oledb\nwind.mdb
!COLLATING_SEQUENCE=Y';
CREATE SERVER spirit
WRAPPER OLEDB
OPTIONS (CONNECTSTRING 'Provider=MSDAORA;Persist Security Info=False;
User ID=guest;password=pwd;Locale Identifier=1033;
OLE DB Services=CLIENTCURSOR;Data Source=spirit');
CREATE USER MAPPING FOR john
SERVER spirit
OPTIONS (REMOTE_AUTHID 'dave', REMOTE_PASSWORD 'mypwd');
CREATE FUNCTION customers ()
RETURNS TABLE (customer_id INTEGER,
name VARCHAR(20),
address VARCHAR(20),
city VARCHAR(20),
state VARCHAR(5),
zip_code INTEGER)
LANGUAGE OLEDB
EXTERNAL NAME 'spirit!demo.customer';
CREATE FUNCTION favorites (varchar(600))
RETURNS TABLE (store_id CHAR (4),
name VARCHAR (41),
sales INTEGER)
SPECIFIC favorites
LANGUAGE OLEDB
EXTERNAL NAME '!!Provider=SQLOLEDB.1;Persist Security Info=False;
User ID=sa;Initial Catalog=pubs;Data Source=WALTZ;
Locale Identifier=1033;Use Procedure for Prepare=1;
Auto Translate=False;Packet Size=4096;Workstation ID=WALTZ;
OLE DB Services=CLIENTCURSOR;';
SELECT *
FROM TABLE (favorites
(' select top 3 sales.stor_id as store_id, ' CONCAT
' stores.stor_name as name, ' CONCAT
' sum(sales. qty) as sales ' CONCAT
' from sales, stores ' CONCAT
' where sales.stor_id = stores.stor_id ' CONCAT
' group by sales.stor_id, stores.stor_name ' CONCAT
' order by sum(sales.qty) desc ')) as f;