Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 17.1 Looking at the ProblemChapter 17
Calling PL/SQL Functions in SQL
Next: 17.3 Requirements for Stored Functions in SQL
 

17.2 Syntax for Calling Stored Functions in SQL

You call a stored function from within a SQL expression using the same syntax as in a PL/SQL expression:

[schema_name.][pkg_name.][func_name[@db_link_name][parameter_list]

where schema_name is the optional name of the schema in which the function is defined (the schema is usually your own Oracle account), pkg_name is the optional name of the package in which the function is defined (if it is not a standalone function), func_name is the name of the function, db_link_name is the optional name of the database link if you are executing a remote procedure call, and parameter_list is the optional list of parameters for the function.

Suppose that the calc_sales function is defined as follows:

FUNCTION calc_sales
   (company_id_in IN company.company_id%TYPE,
    status_in IN order.status_code%TYPE := NULL)
RETURN NUMBER;

then here are some different ways it might be called inside SQL:

SELECT sales_pkg.calc_sales (1001, 'O')
  FROM orders;
SELECT sales_pkg.calc_sales@NEW_YORK (1001, 'O')
  FROM orders;

SQL will properly parse all of these variations, but you should always avoid hardcoding the module's schema and database link directly in your SQL statements (as shown in the third and fourth bullets). Instead, you should create synonyms that hide this information. That way, if you ever need to change the owner of the function or move it to a different database instance, you will have to change only the synonym, as opposed to all the individual SQL statements that call that function.

When you use a stored function in a SQL statement, you must use positional notation; named and mixed notations are not allowed. You can only call calc_sales by listing both arguments in their positional order.


Previous: 17.1 Looking at the ProblemOracle PL/SQL Programming, 2nd EditionNext: 17.3 Requirements for Stored Functions in SQL
17.1 Looking at the ProblemBook Index17.3 Requirements for Stored Functions in SQL

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