----------------------------------------------------------------------------
-----------------ORA错误处理系列 By Cryking---------------------
------------------------转载请注明出处,谢谢!------------------------- 

错误PL/SQL语句:

declareTYPE id_tt IS TABLE OF NUMBER;v_t id_tt := id_tt(1, 2, 3, 4, 5);
beginfor x in (SELECT column_value FROM TABLE(CAST(v_t as id_tt))) loopdbms_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
5PL/SQL procedure successfully completed.Elapsed: 00:00:00.01