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

Question

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.

The type "number_table" you created inside the package cannot be called in an SQL statement. SQL can only see user defined type variables at the schema level. — alexs, Feb 02 '18 at 10:59

score 6 · Accepted Answer · answered Feb 02 '18 at 03:04

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>

score 2 · Answer 2 · answered Feb 02 '18 at 03:45

You can open a cursor and then return the cursor for the caller to fetch from:

FUNCTION OPEN_EMPLOYEE_CURSOR IS
  csrEmployees  SYS_REFCURSOR;
BEGIN
  OPEN csrEmployees FOR
    SELECT FIRST_NAME,
           LAST_NAME,
           EMPLOYEE_ID,
           ADDRESS_1,
           ADDRESS_2,
           ADDRESS_3,
           CITY,
           STATE,
           POSTAL_CODE,
           COUNTRY
      FROM EMPLOYEES;

  RETURN csrEmployees;
END;

The caller should be careful to close the cursor when they're done with it.

Best of luck.

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

2 Answers2