CREATE FUNCTION

The CREATE FUNCTION statement creates a standalone stored function.

Required Privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     RETURN DataType 
     [InvokerRightsClause][AccessibleByClause][DETERMINISTIC]
     {IS|AS} PlsqlFunctionBody

InvokerRightsClause::=
AUTHID {CURRENT_USER|DEFINER}

AccessibleByClause::=
ACCESSIBLE BY (accessor[,...])

accessor::= 
[UnitKind][Owner.]UnitName

You can specify InvokerRightsClause, AccessibleByClause, or DETERMINISTIC in any order.

Parameters

Parameter	Description
OR REPLACE	Specify OR REPLACE to recreate the function if it already exists. Use this clause to change the definition of an existing function without dropping and recreating it. When you recreate a function, TimesTen recompiles it.
FunctionName	Name of function.
arguments	Name of argument or parameter. You can specify 0 or more parameters for the function. If you specify a parameter, you must specify a data type for the parameter. The data type must be a PL/SQL data type.
IN|OUT|IN OUT	Parameter modes. IN is a read-only parameter. You can pass the parameter's value into the function but the function cannot pass the parameter's value out of the function and back to the calling PL/SQL block. The value of the parameter cannot be changed. OUT is a write-only parameter. Use an OUT parameter to pass a value back from the function to the calling PL/SQL block. You can assign a value to the parameter. IN OUT is a read/write parameter. You can pass values into the function and return a value back to the calling program (either the original, unchanged value or a new value set within the function. IN is the default.
NOCOPY	Specify NOCOPY to instruct TimesTen to pass the parameter as fast as possible. You can enhance performance when passing a large value such as a record, an index-by-table, or a varray to an OUT or IN OUT parameter. IN parameters are always passed NOCOPY.
DEFAULT expr	Use this clause to specify a default value for the parameter. You can specify := in place of the keyword DEFAULT.
RETURN DataType	Required clause. A function must return a value. You must specify the data type of the return value of the function. Do not specify a length, precision, or scale for the data type. The data type is a PL/SQL data type.
InvokerRightsClause	Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows: Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default. Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it. For more information, see Definer's Rights and Invoker's Rights (AUTHID Clause) in the Oracle TimesTen In-Memory Database Security Guide.
AccessibleByClause	Use this clause to specify one or more accessors (PL/SQL units) that can invoke the function directly. The list of accessors that can access the function is called a white list. A white list gives you the ability to add an extra layer of security to your PL/SQL objects. Specifically, you can restrict access to the function to only those objects on the white list. AccessibleByClause can appear only once in the CREATE FUNCTION statement. Syntax: ACCESSIBLE BY (accessor [,...])
accessor	Used in AccessibleByClause. An accessor is a PL/SQL unit that can invoke the function. An accessor can appear more than once in the AccessibleByClause clause. Syntax: [UnitKind][Owner.]UnitName
UnitKind	Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the kind of PL/SQL unit that can invoke the function. UnitKind is optional, but if specified, valid options are: FUNCTION PROCEDURE PACKAGE
[Owner.]UnitName	Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the name of the PL/SQL unit that can invoke the function. If you specify UnitKind, then UnitName must be a name of a unit of that kind. For example, if you specify PROCEDURE for UnitKind, then UnitName must be the name of a procedure. UnitName is required. You can optionally specify Owner. If you specify Owner, then UnitName must reside in that owner's schema. If you do not specify Owner, UnitName must be in the schema that contains the function.
DETERMINISTIC	Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its parameters.
IS|AS	Specify either IS or AS to declare the body of the function.
plsql_function_spec	Specifies the function body.

Description

• AccessibleByClause:

  • The compiler checks the validity of the syntax of the ACCESSIBLE BY clause, but does not check that the accessor exists. Therefore, you can define an accessor that does yet exist in the owner's schema.

  • When you invoke the function, the compiler first does the normal permission checks on the invocation. If any check fails, the invocation fails, even if the invoker is an accessor. If all normal permission checks on the invocation succeed, and the function has no ACCESSIBLE BY clause, the invocation succeeds. If the function has an ACCESSIBLE BY clause, the invocation succeeds only if the invoker is an accessor.

• When you create or replace a function, the privileges granted on the function remain the same. If you drop and recreate the object, the object privileges that were granted on the original object are revoked.

• In a replication environment, the CREATE FUNCTION statement is not replicated. For more information, see Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in the Oracle TimesTen In-Memory Database Replication Guide.

• TimesTen does not support:

  • parallel_enable_clause

    You can specify this clause, but it has no effect.

  • call_spec clause

  • AS EXTERNAL clause

Examples

Using the Accessible By Clause

This example creates the ProtectedFunction function. The ACCESSIBLE BY clause is used to restrict the invocation of the function to the CallingProc1 and CallingProc2 procedures. Note that for CallingProc1, the type of PL/SQL unit is not specified and for CallingProc2, the type of PL/SQL unit is specified (PROCEDURE).

Command> CREATE OR REPLACE FUNCTION ProtectedFunction (a IN NUMBER)
           RETURN NUMBER
         ACCESSIBLE BY (CallingProc1, PROCEDURE CallingProc2)
         AS
         BEGIN
           RETURN a * 1;
         END;
         /
         
Function created.

Create the CallingProc1 and CallingProc2 procedures.

Command> CREATE OR REPLACE PROCEDURE CallingProc1 AS
           a NUMBER:=1;
         BEGIN
           a:=ProtectedFunction(a);
           DBMS_OUTPUT.PUT_LINE ('Calling Procedure: '|| a);
         END;
         /
         
Procedure created.

Command> CREATE OR REPLACE PROCEDURE CallingProc2
         AS
           a NUMBER:=2;
         BEGIN
           a:=ProtectedFunction(a);
           DBMS_OUTPUT.PUT_LINE ('Calling Procedure: '|| a);
         END;
         /
         
Procedure created.

Call the procedures. CallingProc1 and CallingProc2 are in the white list, resulting in successful execution.

Command> SET SERVEROUTPUT ON
Command> exec CallingProc1;
Calling Procedure: 1
 
PL/SQL procedure successfully completed.
 
Command> exec CallingProc2;
Calling Procedure: 2
 
PL/SQL procedure successfully completed.

Illustrating the Syntax for Creating a PL/SQL Function

Create function get_sal with one input parameter. Return salary as type NUMBER.

Command> CREATE OR REPLACE FUNCTION get_sal
           (p_id employees.employee_id%TYPE) RETURN NUMBER IS
           v_sal employees.salary%TYPE := 0;
         BEGIN
           SELECT salary INTO v_sal FROM employees
             WHERE employee_id = p_id;
           RETURN v_sal;
         END get_sal;
         /
 
Function created.