无效的游标???高手请进……急！！

显示全部楼层 · 2010-1-4 08:33:08

在开发过程中需要用到动态游标但使用时出错如下：
1declare
2 TYPE CurTyp IS REF CURSOR;
3 test_cv CurTyp;
4 type idType is table of test1.id%type;
5 type descType is table of test1.description%type;
6 v_id idType;
7 v_desc descType;
8 sqlStmt varchar2(100);
9BEGIN
10 sqlStmt := 'SELECT id,description FROM test1';
11 OPEN test_cv FOR sqlStmt;
12 FETCH test_cv bulk collect INTO v_id,v_desc;
13* END;
14/
declare
*
ERROR 位于第 1 行:
ORA-01001: 无效的游标
ORA-06512: 在line 12

千问 · 2010-1-4 08:33:08

1declare
2 TYPE CurTyp IS REF CURSOR;
3 test_cv CurTyp;
4 type idType is table of test1.id%type;
5 type descType is table of test1.description%type;
6 v_id test1.id%type;
7 v_desc test1.description%type;
8 sqlStmt varchar2(100);
9BEGIN
10 sqlStmt := 'SELECT id,description FROM test1';
11 OPEN test_cv FOR sqlStmt;
12 FETCH test_cv INTO v_id,v_desc;
13* END;
SQL> /
PL/SQL 过程已成功完成。

千问 · 2010-1-4 08:33:08

如果不使用动态游标也不会出错？？？？？
呜呼……
可我又一定需要动态游标和BULK COLLECT
我该怎么办？？？
1declare
2 TYPE CurTyp IS REF CURSOR;
3 test_cv CurTyp;
4 type idType is table of test1.id%type;
5 type descType is table of test1.description%type;
6 v_id idType ;
7 v_desc descType ;
8 sqlStmt varchar2(100);
9BEGIN
10 --sqlStmt := 'SELECT id,description FROM test1';
11 OPEN test_cv FOR SELECT id,description FROM test1;
12 FETCH test_cv BULK COLLECT INTO v_id,v_desc;
13* END;
SQL> /
PL/SQL 过程已成功完成。