Home » SQL & PL/SQL » SQL & PL/SQL » how do execute this dynamically
how do execute this dynamically [message #36694] Thu, 13 December 2001 00:31 Go to next message
previn
Messages: 3
Registered: December 2001
Junior Member
DECLARE
G_add varchar2(100);
G_ORDER_DATE_FROM Date;
G_ORDER_DATE_TO Date;
G_INS_DATE_FROM Date;
G_INS_DATE_TO Date;

BEGIN
:G_ORDER_DATE_FROM := '&&1';
:G_ORDER_DATE_TO := '&&2';
:G_INS_DATE_FROM := '&&3';
:G_INS_DATE_TO := '&&4';

if :G_INS_DATE_FROM is null then

G_add := 'and (to_date(install_date) between nvl(:G_INS_DATE_FROM,install_date) and nvl(:G_INS_DATE_TO,install_date))';

else

G_add := ' ';

end if;

select order_number, line_number, order_line_id
, part_number, item_description,install_date
, sum(decode(category_type,'ILMI',1,'ILM',1,0)) ilm_count
, sum(decode(category_type, 'ITM',1,0)) itm_count
, sum(decode(category_type,'IDT',1,0)) idt_count
from atrd_serial_num_ship_v sn
where date_ordered between nvl(:G_ORDER_DATE_FROM,date_ordered)
and nvl(:G_ORDER_DATE_TO,date_ordered)
and decode(:G_INS_DATE_FROM,null,null,.......

if :G_INS_DATE_FROM is not null then we need to get the G_add statement in this place. I tried as ||&G_add but it never worked.

group by order_number, line_number, order_line_id
, part_number, item_description,install_date;
END;

----------------------------------------------------------------------
Re: dynamically [message #36719 is a reply to message #36694] Fri, 14 December 2001 05:33 Go to previous message
SAlapati
Messages: 12
Registered: November 2001
Junior Member
Read documentation on Dynamic SQl

and (if using oracle 8i) read doc's on this command
EXECUTE IMMEDIATE

----------------------------------------------------------------------
Previous Topic: fetch question. plz help ;
Next Topic: how to use macro in sqlplus
Goto Forum:
  


Current Time: Thu Aug 06 09:11:03 CDT 2020