Oracle PL/SQL Language Pocket Reference

Oracle PL/SQL Language Pocket ReferenceSearch this book
Previous: 1.14 PackagesChapter 1
Oracle PL/SQL Language Pocket Reference
Next: 1.16 Oracle8 Objects
 

1.15 Calling PL/SQL Functions in SQL

Stored functions can be called from SQL statements in a manner similar to built-in functions like DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way. Unfortunately, there are a number of caveats and restrictions.

The most notable caveat is that stored functions executed from SQL are not guaranteed to follow the read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function will look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.

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:

1.15.3 Calling Packaged Functions in SQL

Prior to Oracle8i 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 Oracle8i 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 (Oracle8i)

 

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:

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.

1.15.3.1 Column/function name precedence

If your function has the same name as a table column in your SELECT statement and the function has no parameter, then the column takes precedence over the function. To force the RDBMS to resolve the name to your function, prepend the schema name to it:

CREATE TABLE emp(new_sal NUMBER ...);
CREATE FUNCTION new_sal RETURN NUMBER IS ...;

SELECT new_sal FROM emp;      -- Resolves to column.
SELECT scott.new_sal FROM emp;-- Resolves to 
                                 function.


Previous: 1.14 PackagesOracle PL/SQL Language Pocket ReferenceNext: 1.16 Oracle8 Objects
1.14 Packages 1.16 Oracle8 Objects

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference