CREATE FUNCTION

Purpose

Use the CREATE FUNCTION statement to create a standalone stored function or a call specification. (You can also create a function as part of a package using the CREATE PACKAGE statement.)

A stored function (also called a user 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 which Java method, or which named function in which shared library, to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.

See Also: CREATE PROCEDURE for a general discussion of procedures and functions "Examples" for examples of creating functions CREATE PACKAGE for information on creating packages ALTER FUNCTION for information on modifying a function CREATE LIBRARY for information on shared libraries DROP FUNCTION for information on dropping a standalone function Oracle8i Application Developer's Guide - Fundamentals for more information about registering external functions

Prerequisites

Before a stored function can be created, the user SYS must run the SQL script 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 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 Oracle8i Java Stored Procedures Developer's Guide for more information on such prerequisites

Syntax

invoker_rights_clause::=

call_spec::=

Java_declaration::=

C_declaration::=

Keywords and Parameters

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, Oracle 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, Oracle 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 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, Oracle 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 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 enforces these restrictions not only for the function 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 that function or any function 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.

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 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.)
	When you specify NOCOPY, assignments made to a package variable may show immediately in this parameter (or assignments made to this parameter may show immediately in a package variable) if the package variable is passed as the actual assignment corresponding to this parameter. Similarly, changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both. If the function is exited with an unhandled exception, any assignment made to this parameter may be visible in the caller's variable.
	These effects may or may not occur on any particular call. You should use NOCOPY only when these effects would not matter.
datatype	Specify the datatype of an argument. An argument can have any datatype supported by PL/SQL.
	The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of an argument from the environment from which the function is called.

RETURN

datatype	Specify the datatype of the function's return value. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.
	The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of the return value from the environment from which the function is called. See Also: PL/SQL User's Guide and Reference for information on PL/SQL datatypes

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 resolves external names in queries, DML operations, and dynamic SQL statements in the function.

AUTHID CURRENT_USER	Specify CURRENT_USER if you want the function to execute with the privileges of CURRENT_USER. This clause creates an "invoker-rights function." This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the function resides.
AUTHID DEFINER	Specify DEFINER if you want the function to execute with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default.

DETERMINISTIC

DETERMINISTIC is an optimization hint that allows the system to use a saved copy of the function's return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function.

The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function.

A function must be declared DETERMINISTIC in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.

See Also: Oracle8i Data Warehousing Guide for information on materialized views CREATE INDEX for information on function-based indexes

PARALLEL_ENABLE

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 may not be shared among the parallel execution servers.

See Also: Oracle8i Application Developer's Guide - Fundamentals

IS | AS

pl/sql_subprogram_body	Declare the function in a PL/SQL subprogram body. See Also: Oracle8i Application Developer's Guide - Fundamentals for more information on PL/SQL subprograms
call_spec	The call_spec lets you 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: - Oracle8i Java Stored Procedures Developer's Guide - Oracle8i Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the C_declaration
	AS EXTERNAL	AS EXTERNAL is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS LANGUAGE C syntax.

Examples

CREATE FUNCTION Examples

The following statement creates the function get_bal.

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT balance 
      INTO acc_bal 
      FROM accounts 
      WHERE account_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 accounts table. The function uses a RETURN statement to return this value to the environment in which the function is called.

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

SELECT get_bal(100) FROM DUAL;

The following statement creates 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.)

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 (...);