oracle-databaseplsqloracle-sqldevelopertable-functions

In PL/SQL how do you return the results of a query from a User-Defined Function?


I'm coming to Oracle 11g from SQL Server, and I'm trying to use SQL Developer to create a simple function (getIDs) that will return the results of a query (SELECT id FROM employee). Nothing that I've found on the internet seems to work. Here is the code that I'm trying:

test_pkg:

CREATE OR REPLACE PACKAGE test_pkg AS 

    TYPE number_table IS TABLE OF NUMBER;

    FUNCTION getIDs RETURN number_table;

END TEST_PKG;

test_pkg body:

CREATE OR REPLACE PACKAGE BODY test_pkg AS

    FUNCTION getIDs RETURN number_table AS
        ids number_table;
    BEGIN
        SELECT id  BULK COLLECT INTO ids 
        FROM employee;
        RETURN ids;
    END getIDs;

END test_pkg;

SQL Developer Worksheet:

SELECT * 
FROM TABLE(test_pkg.getIDs());

-- The above gives 
-- ORA-00902: invalid datatype


DECLARE
    nums test_pkg.number_table;
BEGIN
    SELECT column_value BULK COLLECT INTO nums
    FROM TABLE(test_pkg.getIDs);
END;

-- The above gives:
-- PLS-00642: local collection types not allowed in SQL statements
-- PL/SQL: ORA-22905: cannot access rows from a non-nested table item

If anyone could see what I'm doing wrong here, I'd greatly appreciate it. I've spent a lot of time searching for the answer and it seems like this should work. In the end I'll also want to query the results of the getIDs() function inside other functions, procedures, and queries.


Solution

  • Create the type as a standalone object

    SQL> create or replace
      2  type number_table IS TABLE OF NUMBER;
      3  /
    
    Type created.
    
    SQL>
    SQL> CREATE OR REPLACE PACKAGE test_pkg AS
      2
      3      FUNCTION getIDs RETURN number_table;
      4
      5  END TEST_PKG;
      6  /
    
    Package created.
    
    SQL>
    SQL>
    SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
      2
      3      FUNCTION getIDs RETURN number_table AS
      4          ids number_table;
      5      BEGIN
      6          SELECT employee_Id  BULK COLLECT INTO ids
      7          FROM employees;
      8          RETURN ids;
      9      END getIDs;
     10
     11  END test_pkg;
     12  /
    
    Package body created.
    
    SQL> sho err
    No errors.
    SQL>
    SQL> SELECT *
      2  FROM TABLE(test_pkg.getIDs());
    
    COLUMN_VALUE
    ------------
             100
             101
             102
             103
             104
             105
             106
             107
             108
             109
             110
             111
             112
             113
             114
             115
             116
             117
             118
             119
             120
             121
             122
             123
             124
             125
             126
             127
             128
             129
             130
             131
             132
             133
             134
             135
             136
             137
             138
             139
             140
             141
             142
             143
             144
             145
             146
             147
             148
             149
             150
             151
             152
             153
             154
             155
             156
             157
             158
             159
             160
             161
             162
             163
             164
             165
             166
             167
             168
             169
             170
             171
             172
             173
             174
             175
             176
             177
             178
             179
             180
             181
             182
             183
             184
             185
             186
             187
             188
             189
             190
             191
             192
             193
             194
             195
    
    COLUMN_VALUE
    ------------
             196
             197
             198
             199
             200
             201
             202
             203
             204
             205
             206
    
    107 rows selected.
    
    SQL>
    SQL>
    SQL> DECLARE
      2      nums number_table;
      3  BEGIN
      4      SELECT column_value BULK COLLECT INTO nums
      5      FROM TABLE(test_pkg.getIDs);
      6  END;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL>
    SQL>