PLS-00642

最新推荐文章于 2021-04-10 07:38:58 发布

Cryking

最新推荐文章于 2021-04-10 07:38:58 发布

阅读量4.6k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/edcvf3/article/details/9166423

版权

oracle 专栏收录该内容

92 篇文章 3 订阅

订阅专栏

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

错误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