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.
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>