Skip to Main Content
Oracle PL/SQL Language Pocket Reference, 4th Edition
book

Oracle PL/SQL Language Pocket Reference, 4th Edition

by Steven Feuerstein, Bill Pribyl, Chip Dawes
October 2007
Intermediate to advanced content levelIntermediate to advanced
178 pages
2h 50m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Language Pocket Reference, 4th Edition

Calling PL/SQL Functions in SQL

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

The most notable caveat is that stored functions executed from SQL are not by default guaranteed to follow the statement-level 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 may 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 ISOLATION LEVEL 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.

Calling a Function

The syntax for calling a stored function from SQL is the same as that used to reference 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 required and is the function name. db_link is optional and refers to the database link name to the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Language Pocket Reference, 5th Edition

Oracle PL/SQL Language Pocket Reference, 5th Edition

Steven Feuerstein, Bill Pribyl, Chip Dawes

Publisher Resources

ISBN: 9780596514044Errata Page