这是前几天遇到的一个小问题记录下来,呵呵
错误事例:
declare
type tb_type is table of varchar2(32);
v_coll tb_type:=tb_type();
begin
v_coll:=tb_type('xiaowang','xiaoma','gagaga');
for i in (select * from table(v_coll))
loop
dbms_output.put_line(i.column_value);
end loop;
end;
上述代码执行时报错PLS-00642错误,查找该错误含义:
PLS-00642 local collection types not allowed in SQL statements
Cause: A locally-defined (that is not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.
Action: Define the collection type in your schema, not inside a PL/SQL subprogram.
即:如果在sql级使用嵌套表或varray数组,则所定义的类型必须是schema级的。
正确事例:
create type tb_type is table of varchar2(32);
declare
v_coll tb_type:=tb_type();
begin
v_coll:=tb_type('xiaowang','xiaoma','gagaga');
for i in (select * from table(v_coll))
loop
dbms_output.put_line(i.column_value);
end loop;
end;
错误事例:
declare
type tb_type is table of varchar2(32);
v_coll tb_type:=tb_type();
begin
v_coll:=tb_type('xiaowang','xiaoma','gagaga');
for i in (select * from table(v_coll))
loop
dbms_output.put_line(i.column_value);
end loop;
end;
上述代码执行时报错PLS-00642错误,查找该错误含义:
PLS-00642 local collection types not allowed in SQL statements
Cause: A locally-defined (that is not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.
Action: Define the collection type in your schema, not inside a PL/SQL subprogram.
即:如果在sql级使用嵌套表或varray数组,则所定义的类型必须是schema级的。
正确事例:
create type tb_type is table of varchar2(32);
declare
v_coll tb_type:=tb_type();
begin
v_coll:=tb_type('xiaowang','xiaoma','gagaga');
for i in (select * from table(v_coll))
loop
dbms_output.put_line(i.column_value);
end loop;
end;