1.15.1 Syntax for Calling Stored Functions in SQL

The syntax for calling a stored function from SQL is the same as referencing it from PL/SQL:

[schema_name.][pkg_name.]func_name[@db_link]    [parm_list]

schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is mandatory and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.

The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in the Section 1.14.1, "Overview of Package Structure " section:

-- Capture system events. INSERT INTO v_sys_event (timestamp ,event     ,qty_waits)    SELECT time_pkg.GetTimestamp ,event ,total_waits    FROM v$system_event  -- Capture system statistics. INSERT INTO v_sys_stat (timestamp,stat#,value)    SELECT time_pkg.GetTimestamp ,statistic# ,value    FROM v$sysstat;

1.15.2 Requirements and Restrictions on Stored Functions in SQL

There are a number of requirements for calling stored functions in SQL:

• The function must be a single-row function -- not one that operates on a column or group function.

• All parameters must be IN; no IN OUT or OUT parameters are allowed.

• The datatypes of the function's parameters and RETURN must be compatible with RDBMS datatypes. You cannot have arguments or RETURN types like BOOLEAN, programmer-defined record, index-by table, etc.

• The parameters passed to the function must use positional notation; named notation is not supported.

• Functions defined in packages must have a RESTRICT_REFERENCES pragma in the specification (Oracle8.0 and earlier).

• The function must be stored in the database, not a local program, Developer/2000 PL/SQL library, or Form.

1.15.3 Calling Packaged Functions in SQL

Prior to Oracle8 i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8 i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.

The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:

PRAGMA RESTRICT_REFERENCES (program_name |     DEFAULT, purity_level);

The keyword DEFAULT applies to all methods of an object type or all programs in a package.

There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects . Side effects are listed in the following table with the purity levels they address.

Purity Level	Description	Restriction
WNDS	Write No Database State	Executes no INSERT, UPDATE, or DELETE statements.
RNDS	Read No Database State	Executes no SELECT statements.
WNPS	Write No Package State	Does not modify any package variables.
RNPS	Read No Package State	Does not read any package variables.
TRUST (Oracle8 i )		Does not enforce the restrictions declared but allows the compiler to trust they are true.

The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:

• To be called from SQL, all stored functions must assert WNDS.

• All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.

• To be executed remotely, the function must assert WNPS and RNPS.

• To be executed in parallel, the function must assert all four purity levels or, in Oracle8 i , use PARALLEL_ENABLED in the declaration.

• These functions must not call any other program that does not also assert the minimum purity level.

• If a package has an initialization section, it too must assert purity in Oracle7.

• If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.

Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited.