Local function may not be used. Invalid identifier-CodePudding

I have a function and procedure. A procedure calls a function. When I create that function and procedure as schema objects using create or replace, then everything works fine:

create or replace FUNCTION is_highest_paid (
        emp_id employees.employee_id%TYPE,
        avg_sal NUMBER
    ) RETURN CHAR AS
        is_highest CHAR(1);
    BEGIN
        SELECT 'Y'
        INTO is_highest
        FROM employees
        WHERE employee_id = emp_id
            AND salary > avg_sal;

        RETURN is_highest;
    END;
    
create or replace PROCEDURE fill_high_paid_emps AS        
        avg_sal NUMBER;
    BEGIN
        SELECT AVG(salary)
        INTO avg_sal
        FROM employees;

        INSERT INTO highest_paid_employees
        SELECT *
        FROM employees
        WHERE is_highest_paid(employees.employee_id, avg_sal) = 'Y';
    END; 
begin
    fill_high_paid_emps();
end;

But as soon as I try to use them locally in an anonymous block like that:

DECLARE
    FUNCTION is_highest_paid (
        emp_id employees.employee_id%TYPE,
        avg_sal NUMBER
    ) RETURN CHAR AS
        is_highest CHAR(1);
    BEGIN
        SELECT 'Y'
        INTO is_highest
        FROM employees
        WHERE employee_id = emp_id
            AND salary > avg_sal;

        RETURN is_highest;
    END;

    PROCEDURE fill_high_paid_emps AS        
        avg_sal NUMBER;
    BEGIN
        SELECT AVG(salary)
        INTO avg_sal
        FROM employees;

        INSERT INTO highest_paid_employees
        SELECT *
        FROM employees
        WHERE is_highest_paid(employees.employee_id, avg_sal) = 'Y';
    END;
BEGIN
    fill_high_paid_emps();
END;

I get: PLS-00231: function 'IS_HIGHEST_PAID' may not be used in SQL

PL/SQL: ORA-00904: : invalid identifier

What's the reason of that?

CodePudding user response：

You have declared the function in the PL/SQL scope; it can only be used in PL/SQL and is not visible in the SQL scope.

You can either:

Declare it in SQL using CREATE FUNCTION;
Declare it in a package using CREATE PACKAGE; or
If you are using Oracle 12 or later, declare it inline in the SQL query WITH FUNCTION ....

CodePudding user response：

That's because function has to be created at SQL level - either by CREATE OR REPLACE FUNCTION is_highest_paid (as you already showed us), or within a package.

It just won't work (calling it from a SQL statement) if it is a local function - local to your anonymous PL/SQL block.