17.2 Syntax for Calling Stored Functions in SQL

Chapter 17
Calling PL/SQL 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:

• As a standalone function:

  SELECT calc_sales (1001, 'O')   FROM orders;

• As a package-based function:

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

• As a remote, package-based function call:

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

• As a standalone function in a specific schema:

  SELECT scott.calc_sales (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.