Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic cursor using ref cursor
Dynamic cursor using ref cursor [message #352435] Tue, 07 October 2008 11:17 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have created one procedure which is given below after i pharse the open-for statement i will shown an also given below it is very urgent can anyone help me to sort this


create or replace procedure risi (paycen varchar2,
acc number
risi_adv out ei_emp%rowtype)
as
sql_query varchar2(100);
type adv_srch is ref cursor return ei_emp%rowtype;
advsrc adv_srch;

begin

sql_query:='select pay_cen,acc_ei,emp from adv_ei where';

if (paycen is not null) then
 sql_query:=sql_query||' '||'AND'||'pay_cen='||paycen;
elsif (acc is not null) then
 sql_query:=sql_query||' '||'AND'||'acc_ei ='||acc;
end if;

sql_query:=sql_query||';';

open advsrc for sql_query;
end ;


Error as : PLS-00455:cursor 'ADVSRC' cannot be used in dynamic SQL OPEN statement

[Updated on: Tue, 07 October 2008 11:19]

Report message to a moderator

Re: Dynamic cursor using ref cursor [message #352492 is a reply to message #352435] Wed, 08 October 2008 01:13 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00455: cursor "string" cannot be used in dynamic SQL OPEN statement
Cause: dynamicly OPEN a REF CURSOR that has RETURN type. However, only REF CURSOR without RETURN type can be OPEN"ed by an embebded dynamic OPEN statement.
Action: define a REF CURSOR without return type, and use it in the statement.

Use predefined sys_refcursor type.

Regards
Michel
Previous Topic: unique field
Next Topic: Invalid data type
Goto Forum:
  


Current Time: Mon Dec 05 14:57:16 CST 2016

Total time taken to generate the page: 0.07983 seconds