Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 17.5 Calling Packaged Functions in SQLChapter 17
Calling PL/SQL Functions in SQL
Next: 17.7 Realities: Calling PL/SQL Functions in SQL
 

17.6 Column/Function Name Precedence

If your function has the same name as a table column in your SELECT statement and it has no parameters, then the column takes precedence over the function.

The employee table has a column named "salary." Suppose you create a function named salary as well:

CREATE TABLE employee (employee_id NUMBER, ... , salary NUMBER, ...);

FUNCTION salary RETURN NUMBER;

Then a SELECT statement referencing salary always refers to the column and not the function:

SELECT salary INTO calculated_salary FROM employee;

If you want to override the column precedence, you must qualify the name of the function with the name of the schema that owns the function, as follows:

SELECT scott.salary INTO calculated_salary FROM employee;

This now executes the function instead of retrieving the column value.


Previous: 17.5 Calling Packaged Functions in SQLOracle PL/SQL Programming, 2nd EditionNext: 17.7 Realities: Calling PL/SQL Functions in SQL
17.5 Calling Packaged Functions in SQLBook Index17.7 Realities: Calling PL/SQL 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