Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE FUNCTION

Purpose

Use the CREATE FUNCTION statement to create a standalone stored function or a call specification.

A stored function (also called a user function or user defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call specification tells Oracle Database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.


Note:

You can also create a function as part of a package using the CREATE PACKAGE statement.


See Also:


Prerequisites

Before a stored function can be created, the user SYS must run a SQL script that is commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a function in another user's schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call specification, you may need additional privileges, for example, EXECUTE privileges on a C library for a C call specification.

To embed a CREATE FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.


See Also:

PL/SQL User's Guide and Reference or Oracle Database Java Developer's Guide for more information on such prerequisites

Syntax


create_function::=
Description of create_function.gif follows
Description of the illustration create_function.gif

(invoker_rights_clause::=, parallel_enable_clause ::=)


invoker_rights_clause ::=
Description of invoker_rights_clause.gif follows
Description of the illustration invoker_rights_clause.gif


parallel_enable_clause ::=
Description of parallel_enable_clause.gif follows
Description of the illustration parallel_enable_clause.gif


streaming_clause::=
Description of streaming_clause.gif follows
Description of the illustration streaming_clause.gif


call_spec::=
Description of call_spec.gif follows
Description of the illustration call_spec.gif


Java_declaration::=
Description of Java_declaration.gif follows
Description of the illustration Java_declaration.gif


C_declaration::=
Description of C_declaration.gif follows
Description of the illustration C_declaration.gif

Semantics


OR REPLACE

Specify OR REPLACE to re-create the function if it already exists. Use this clause to change the definition of an existing function without dropping, re-creating, and regranting object privileges previously granted on the function. If you redefine a function, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges.

If any function-based indexes depend on the function, then Oracle Database marks the indexes DISABLED.


See Also:

ALTER FUNCTION for information on recompiling functions


schema

Specify the schema to contain the function. If you omit schema, Oracle Database creates the function in your current schema.


function

Specify the name of the function to be created. If creating the function results in compilation errors, then Oracle Database returns an error. You can see the associated compiler error messages with the SHOW ERRORS command.

Restrictions on User-Defined Functions
  • User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:

    • In a CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement

    • In a DEFAULT clause of a CREATE TABLE or ALTER TABLE statement

  • In addition, when a function is called from within a query or DML statement, the function cannot:

    • Have OUT or IN OUT parameters

    • Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.

    • Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database.

    • Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.

Except for the restriction on OUT and IN OUT parameters, Oracle Database enforces these restrictions not only for function when called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by function or any functions it calls.


argument

Specify the name of an argument to the function. If the function does not accept arguments, you can omit the parentheses following the function name.


Restriction on Function Arguments

If you are creating an aggregate function, you can specify only one argument.


IN

Specify IN to indicate that you must supply a value for the argument when calling the function. This is the default.


OUT

Specify OUT to indicate that the function will set the value of the argument.


IN OUT

Specify IN OUT to indicate that a value for the argument can be supplied by you and may be set by the function.


NOCOPY

Specify NOCOPY to instruct Oracle Database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an OUT or IN OUT parameter. IN parameter values are always passed NOCOPY.

These effects may or may not occur on any particular call. You should use NOCOPY only when these effects would not matter.


RETURN Clause

For datatype, specify the datatype of the return value of the function. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.


Note:

Oracle SQL does not support calling of functions with boolean parameters or returns. Therefore, if your user-defined functions will be called from SQL statements, you must design them to return numbers (0 or 1) or character strings ('TRUE' or 'FALSE').

The datatype cannot specify a length, precision, or scale. Oracle Database derives the length, precision, or scale of the return value from the environment from which the function is called.

If the return type is SYS.AnyDataSet and you intend to use the function in the FROM clause of a query, then you must also specify the PIPELINED clause and define a describe method (ODCITableDescribe) as part of the implementation type of the function.


See Also:



invoker_rights_clause

The invoker_rights_clause lets you specify whether the function executes with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER.

This clause also determines how Oracle Database resolves external names in queries, DML operations, and dynamic SQL statements in the function.


AUTHID Clause

DETERMINISTIC Clause

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than re-executing the function.

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.

The following semantic rules govern the use of the DETERMINISTIC clause:


parallel_enable_clause

PARALLEL_ENABLE is an optimization hint indicating that the function can be executed from a parallel execution server of a parallel query operation. The function should not use session state, such as package variables, as those variables are not necessarily shared among the parallel execution servers.

The columns specified in all of these optional clauses refer to columns that are returned by the REF CURSOR argument of the function.


PIPELINED Clause

Specify PIPELINED to instruct Oracle Database to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray). You query table functions by using the TABLE keyword before the function name in the FROM clause of the query. For example:

SELECT * FROM TABLE(function_name(...))

Oracle Database then returns rows as they are produced by the function.


AGGREGATE USING Clause

Specify AGGREGATE USING to identify this function as an aggregate function, or one that evaluates a group of rows and returns a single row. You can specify aggregate functions in the select list, HAVING clause, and ORDER BY clause.

When you specify a user-defined aggregate function in a query, you can treat it as an analytic function (one that operates on a query result set). To do so, use the OVER analytic_clause syntax available for built-in analytic functions. See "Analytic Functions " for syntax and semantics.

In the USING clause, specify the name of the implementation type of the function. The implementation type must be an object type containing the implementation of the ODCIAggregate routines. If you do not specify schema, Oracle Database assumes that the implementation type is in your own schema.


Restriction on Creating Aggregate Functions

If you specify this clause, you can specify only one input argument for the function.


IS | AS Clause

Use the appropriate part of this clause to declare the body of the function.


pl/sql_subprogram_body

Use the pl/sql_subprogram_body to declare the function in a PL/SQL subprogram body.


call_spec

Use the call_spec to map a Java or C method name, parameter types, and return type to their SQL counterparts. In Java_declaration, 'string' identifies the Java implementation of the method.


See Also:



AS EXTERNAL

In earlier releases, AS EXTERNAL was an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle recommends that you use the AS LANGUAGE C syntax.

Examples


Creating a Function: Examples

The following statement creates the function get_bal on the sample table oe.orders (the PL/SQL is in italics):

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
BEGIN 
   SELECT order_total 
   INTO acc_bal 
   FROM orders 
   WHERE customer_id = acc_no; 
   RETURN(acc_bal); 
 END;
/

The get_bal function returns the balance of a specified account.

When you call the function, you must specify the argument acc_no, the number of the account whose balance is sought. The datatype of acc_no is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the balance column from the row identified by the argument acc_no in the orders table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created in the preceding example can be used in a SQL statement. For example:

SELECT get_bal(165) FROM DUAL; 

GET_BAL(165)
------------
        2519

The following statement creates a PL/SQL standalone function get_val that registers the C routine c_get_val as an external function. (The parameters have been omitted from this example; the PL/SQL is in italics.)

CREATE FUNCTION get_val
   ( x_val IN NUMBER,
    y_val IN NUMBER,
    image IN LONG RAW )
   RETURN BINARY_INTEGER AS LANGUAGE C
NAME "c_get_val"
   LIBRARY c_utils
   PARAMETERS (...);

Creating Aggregate Functions: Example

The next statement creates an aggregate function called SecondMax to aggregate over number values. It assumes that the object type SecondMaxImpl routines contains the implementations of the ODCIAggregate routines:

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
    PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

See Also:

Oracle Data Cartridge Developer's Guide for the complete implementation of type and type body for SecondMaxImpl

You would use such an aggregate function in a query like the following statement, which queries the sample table hr.employees:

SELECT SecondMax(salary), department_id
      FROM employees
      GROUP BY department_id
      HAVING SecondMax(salary) > 9000;

SECONDMAX(SALARY) DEPARTMENT_ID
----------------- -------------
            13500            80
            17000            90

Using a Packaged Procedure in a Function: Example

The following statement creates a function that uses a DBMS_LOB.GETLENGTH procedure to return the length of a CLOB column:

CREATE OR REPLACE FUNCTION text_length(a CLOB) 
   RETURN NUMBER DETERMINISTIC IS
BEGIN 
  RETURN DBMS_LOB.GETLENGTH(a);
END;

See Also:

"Creating a Function-Based Index on a LOB Column: Example" to see how to use this function to create a function-based index