Oracle in a Nutshell

Common Attributes

The following keywords can be used in defining either a procedure or a function:

OR REPLACE

Used to rebuild an existing program unit, preserving its privileges.

AUTHID

Defines whether the program will execute with the privileges of, and resolve names like, the object owner (DEFINER) or as the user executing the function (CURRENT_USER). Prior to Oracle8i, only the built-in packages DBMS_SQL and DBMS_UTILITY executed as CURRENT_USER. The default AUTHID is DEFINER. New with Oracle8i.

Tip

Stored SQL supports two models for addressing privileges at runtime. The default, definer rights, tells Oracle to use the privileges of the owner or definer of the program. With the definer rights model, the owner of the program must have the required prvileges granted directly to him—he cannot inherit the privileges from a role.With invoker rights, the user who executes the program does so using his own privileges. Anonymous PL/SQL blocks always execute with invoker rights. To create a program that uses the invoker rights model, specify AUTHID CURRENT_USER in your program’s declaration.

AGGREGATE USING

Required for aggregate functions. Tells Oracle that the function evaluates a group of rows and returns a single result. The built-in AVG is an aggregate function. New with Oracle9i.

DETERMINISTIC

Required for function-based indexes. A function is DETERMINISTIC if it always returns the same value when called with the same parameters. Deterministic functions do not meaningfully reference ...