CREATE FUNCTION statement

There are five different types of functions that can be created using this statement. Each of these is described separately.

• External Scalar. The function is written in a programming language and returns a scalar value. The external executable is registered in the database, along with various attributes of the function.
• External Table. The function is written in a programming language and returns a complete table. The external executable is registered in the database along with various attributes of the function.
• OLE DB External Table. A user-defined OLE DB external table function is registered in the database to access data from an OLE DB provider.
• Sourced or Template. A source function is implemented by invoking another function (either built-in, external, SQL, or source) that is already registered in the database.

  It is possible to create a partial function, called a function template, which defines what types of values are to be returned, but which contains no executable code. The user maps it to a data source function within a federated system, so that the data source function can be invoked from a federated database. A function template can be registered only with an application server that is designated as a federated server.

• SQL Scalar, Table or Row. The function body is written in SQL and defined together with the registration in the database. It returns a scalar value, a table, or a single row.
• Aggregate interface. A aggregate interface function is implemented by invoking several external procedures and an external function which are referred to as component routines.

The CREATE FUNCTION statement can be submitted in obfuscated form. In an obfuscated statement, only the function name and its parameters are readable. The rest of the statement is encoded in such a way that is not readable but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.