Skip to Main Content
Oracle PL/SQL Language Pocket Reference
book

Oracle PL/SQL Language Pocket Reference

by Bill Pribyl, Steven Feuerstein, Chip Dawes
April 1999
Intermediate to advanced content levelIntermediate to advanced
99 pages
2h 4m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Language Pocket Reference

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.

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 ...

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
Pro Oracle Spatial for Oracle Database 11g

Pro Oracle Spatial for Oracle Database 11g

Albert Godfrind, Euro Beinat, Ravi Kothuri

Publisher Resources

ISBN: 1565924576Catalog PageErrata