Home » SQL & PL/SQL » SQL & PL/SQL » ERROR : PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement
ERROR : PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement [message #228115] Sat, 31 March 2007 17:49 Go to next message
m_arafa
Messages: 36
Registered: June 2006
Member
hi,

declare
type rec is record (id number, name varchar2(20));
type cur_typ is ref cursor return rec;
cur cur_typ ;
cond1 varchar2(30) := 'sal > 2000';
begin
open cur for 'select empno, ename from emp where '|| cond1;
end;
/

i must *and must* use ref cursor type "cur" and return record type "rec"
this code work properly without adding to cursor declaration "return rec"

i get that error :-

what the solution ,but with use "return rec" option ???

ERROR at line 7:
ORA-06550: line 7, column 9:
PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored

thanks,
Re: ERROR : PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement [message #228186 is a reply to message #228115] Mon, 02 April 2007 00:49 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
cur is a variable of cur_type, which is a strong typed ref cursor.
You are not allowed to use dynamic sql for a strong typed ref cursor, since Oracle cannot check if the returned structure complies with the structure you claim it will return.
Re: ERROR : PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement [message #229186 is a reply to message #228186] Thu, 05 April 2007 15:28 Go to previous message
m_arafa
Messages: 36
Registered: June 2006
Member
ok, thanks Frank
Previous Topic: How to obtain a recordcount
Next Topic: Find foreign keys between two tables
Goto Forum:
  


Current Time: Thu Mar 28 03:31:17 CDT 2024