Cursor with parameters...

[vpv]

I have follow text:

cursor ovd_open ( ind varchar2) is
SELECT iovdacc,dovdcrt,ccusname...
FROM igg.ovd a,...
WHERE iovdacc=iaccacc ...
order by ind;


begin
list_id:=find_item('LIST_DR');
if :list_dr='LIST314' then
tt:=' dovdcrt desc ';
ELSIF :list_dr='LIST315' THEN
tt:=' ccusname';
ELSE
go_block('OVD');
end if;

go_block('OVD');

OPEN ovd_open(tt);
clear_block(NO_VALIDATE);
loop
fetch ovd_open into vd.iovdacc....
exit when ovd_open%notfound;
next_record;
end loop;

But, statement 'order by..' don't work properly..
Why?

 

[sem]

It does work properly! The variable is bound, not substituted, thus your cursor becomes like

SELECT iovdacc,dovdcrt,ccusname... 
FROM igg.ovd a,... 
WHERE iovdacc=iaccacc ...
order by [b]' dovdcrt desc '[/b]

You need lexical variable, or in case of pl/sql, dynamic sql, most probably REF CURSOR.

Regards, Dima

http://seminihin.narod.ru

 

[vpv]

Hm.. How I can use lexical variable in this example?
I try as follow

blk_id:=find_block('OVD');
ss:='SELECT iovdacc,dovdcrt,dovddbt...
FROM igg.ovd a,...
WHERE iovdacc=iaccacc and ...
order by dovdcrt desc';

set_block_property(blk_id,QUERY_DATA_SOURCE_NAME,SS);
go_block('OVD');
Execute_query;

But appear error message 'FRM-41380....'

 

[lewisp]

This is different to your original problem. Do you want to use a dynamic ORDER BY in a cursor or in a Forms block?

If the former, use ref cursors or dynamic SQL.
If the latter, just set the ORDER_BY property of the block.