Oracle PL/SQL Programming, 2nd Edition

• Oracle PL/SQL Programming, 2nd Edition
• Search this book

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.