错误PL/SQL语句:
declare
TYPE id_tt IS TABLE OF NUMBER;
v_t id_tt := id_tt(1, 2, 3, 4, 5);
begin
for x in (SELECT column_value FROM TABLE(CAST(v_t as id_tt))) loop
dbms_output.put_line(x.column_value);
end loop;
end;
错误描述:
ORA-06550: line 6, column 49:
PLS-00642: local collection types not allowed in SQL statements
错误分析:
根据提示可知道在SQL语句中不能使用局部集合类型.
故需要将集合类型设为数据库schema对象.
解决方法:
先创建数据库类型 CREATE OR REPLACE TYPE id_tt IS TABLE OF NUMBER;
再执行PL/SQL块.
00:23:33 SCOTT@orcl> declare
00:26:20 2 v_t id_tt := id_tt(1, 2,3, 4, 5);
00:26:20 3 begin
00:26:21 4 for x in (SELECT column_value FROM TABLE(CAST(v_t as id_tt))) loop
00:26:21 5 dbms_output.put_line(x.column_value);
00:26:21 6 end loop;
00:26:21 7 end;
00:26:22 8 /
1
2
3
4
5
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01