CODEWITHSUNDEEP
sqloracle-database
gngdoan
gngdoan

Reputation: 21

Function returning TABLE and the JOIN in SELECT query?

I want to create a local FUNCTION which returns result as NESTED TABLE in my PROCEDURE. Then, I wish to JOIN the nested table with another table in a SELECT query like this:

PROCEDURE TEST_DEPID (SOR IN OUT SYS_REFCURSOR) AS
 TYPE TAB IS TABLE OF HR.EMPLOYEES.SALARY%TYPE
          INDEX BY BINARY_INTEGER;
FUNCTION GET_SALARY (P_DEPARTMENT_ID NUMBER)
   RETURN TAB                                         
   IS   RETURN_TBL   TAB;
   BEGIN
    SELECT  SALARY
       BULK COLLECT INTO RETURN_TBL
       FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = TRIM(P_DEPARTMENT_ID);

   RETURN RETURN_TBL;
   END GET_SALARY;

 BEGIN
 OPEN SOR FOR
 SELECT * FROM HR.EMPLOYEES JOIN TABLE (GET_SALARY('60')) B ON A.SALARY = B.SALARY;
 END;

And the errors I got are:

[1]:(Error): PLS-00231: function 'GET_SALARY' may not be used in SQL
[2]:(Error): PL/SQL: ORA-00904: : invalid identifier
[3]:(Error): PL/SQL: SQL Statement ignored

Please give me some advice.

Upvotes: 0

Views: 221

Answers (1)

Christian13467
Christian13467

Reputation: 5614

A local function cannot be used inside a SELECT. Only global functions will work.

Oracle uses 2 engines running SQL and PL/SQL. Running your SELECT the SQL engine can access all functions, but only if they are public known. The local function is not known to the public.

Upvotes: 1

Related Questions