早上帮一群友调试一个过程,开始报错如下:
让他提供了代码,大致看了一遍,简单模拟一下:
SQL> drop table goolen;
Table dropped.
SQL> create table goolen as select object_id id,object_name name from dba_objects where rownum <=10;
Table created.
原始代码改写后执行如下:
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 temp2 varchar2(2000);
6 al varchar2(2000);
7 begin
8 al:='';
9 temp := 'select * from goolen';
10 open cur_policy for temp;
11 loop
12 fetch cur_policy into temp2;
13 exit when cur_policy%notfound;
14 al:=al||','||temp2;
15 close cur_policy;
16 end loop;
17 dbms_output.put_line(al);
18 end;
19 /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 12
报错ORA-00932,其实导致这个错误的原因很简单
goolen有id,name两列,但是fetch into的时候只指定了temp2,所以报错了
查看这段代码,其实存在两个问题,首先改第一个错误
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 v_id number;
6 temp2 varchar2(2000);
7 al varchar2(2000);
8 begin
9 al:='';
10 temp := 'select * from goolen';
11 open cur_policy for temp;
12 loop
13 fetch cur_policy into v_id,temp2;
14 exit when cur_policy%notfound;
15 al:=al||','||temp2;
16 close cur_policy;
17 end loop;
18 dbms_output.put_line(al);
19 end;
20 /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 13
改完后不再报 ORA-00932错误,而是报了另外一个错误 ORA-01001;
其实这个错误是因为语法错误导致的,在loop循环里,close游标这一步放到了end loop之前
更改后再次执行:
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 v_id number;
6 temp2 varchar2(2000);
7 al varchar2(2000);
8 begin
9 al:='';
10 temp := 'select * from goolen';
11 open cur_policy for temp;
12 loop
13 fetch cur_policy into v_id,temp2;
14 exit when cur_policy%notfound;
15 al:=al||','||temp2;
16 end loop;
17 close cur_policy;
18 dbms_output.put_line(al);
19 end;
20 /
,ICOL$,I_USER1,CON$,UNDO$,C_COBJ#,I_OBJ#,PROXY_ROLE_DATA$,I_IND1,I_CDEF2,I_PROXY
_ROLE_DATA$_1
PL/SQL procedure successfully completed.
已经OK了。
![](http://img.blog.itpub.net/blog/attachment/201501/21/23249684_1421806313020w.jpg?x-oss-process=style/bb)
让他提供了代码,大致看了一遍,简单模拟一下:
SQL> drop table goolen;
Table dropped.
SQL> create table goolen as select object_id id,object_name name from dba_objects where rownum <=10;
Table created.
原始代码改写后执行如下:
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 temp2 varchar2(2000);
6 al varchar2(2000);
7 begin
8 al:='';
9 temp := 'select * from goolen';
10 open cur_policy for temp;
11 loop
12 fetch cur_policy into temp2;
13 exit when cur_policy%notfound;
14 al:=al||','||temp2;
15 close cur_policy;
16 end loop;
17 dbms_output.put_line(al);
18 end;
19 /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 12
报错ORA-00932,其实导致这个错误的原因很简单
goolen有id,name两列,但是fetch into的时候只指定了temp2,所以报错了
查看这段代码,其实存在两个问题,首先改第一个错误
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 v_id number;
6 temp2 varchar2(2000);
7 al varchar2(2000);
8 begin
9 al:='';
10 temp := 'select * from goolen';
11 open cur_policy for temp;
12 loop
13 fetch cur_policy into v_id,temp2;
14 exit when cur_policy%notfound;
15 al:=al||','||temp2;
16 close cur_policy;
17 end loop;
18 dbms_output.put_line(al);
19 end;
20 /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 13
改完后不再报 ORA-00932错误,而是报了另外一个错误 ORA-01001;
其实这个错误是因为语法错误导致的,在loop循环里,close游标这一步放到了end loop之前
更改后再次执行:
SQL> declare
2 type cur_type1 is ref cursor;
3 cur_policy cur_type1;
4 temp varchar2(2000);
5 v_id number;
6 temp2 varchar2(2000);
7 al varchar2(2000);
8 begin
9 al:='';
10 temp := 'select * from goolen';
11 open cur_policy for temp;
12 loop
13 fetch cur_policy into v_id,temp2;
14 exit when cur_policy%notfound;
15 al:=al||','||temp2;
16 end loop;
17 close cur_policy;
18 dbms_output.put_line(al);
19 end;
20 /
,ICOL$,I_USER1,CON$,UNDO$,C_COBJ#,I_OBJ#,PROXY_ROLE_DATA$,I_IND1,I_CDEF2,I_PROXY
_ROLE_DATA$_1
PL/SQL procedure successfully completed.
已经OK了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23249684/viewspace-1408988/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23249684/viewspace-1408988/