Home » SQL & PL/SQL » SQL & PL/SQL » how to use macro in sqlplus
how to use macro in sqlplus [message #36712] Thu, 13 December 2001 23:03 Go to next message
michael ni
Messages: 1
Registered: December 2001
Junior Member
please see the folling program,

set serveroutput on 1
declare 2
p_item_id1 number := 1111 ; 3
p_item_id2 number := 2222 ; 4
p_item_id3 number := 3333 ; 5
cnt number; 6
tmp varchar2(20); 7
begin 8
for cnt in 1..3 loop 9
tmp := 'p_item_id'|| to_char(cnt) ; 10
dbms_output.put_line( tmp ); 11
-- dbms_output.put_line( &tmp ); 12
end loop ; 13
end ; 14


result (Execute in sqlplus environment):
p_item_id1
p_item_id2
p_item_id3

I want the result as the following:
1111
2222
3333

I used line 12 replaced line 11,but failed,could you help me(can I use macro to resolve this problem).

Best regards,

Michael Ni



----------------------------------------------------------------------
Re: how to use macro in sqlplus [message #36721 is a reply to message #36712] Fri, 14 December 2001 06:16 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you cant do like the way you are doing. but there is an alternative to do that.
look at code below:

declare
type t1 is table of number index by binary_integer;
p_item_id t1;
cnt number;
tmp varchar2(20);
begin
p_item_id(1) := 1111 ;
p_item_id(2) := 2222 ;
p_item_id(3) := 3333 ;
for cnt in 1..3 loop
tmp:= p_item_id(cnt);
dbms_output.put_line( tmp );
end loop ;
end ;

SURESH

----------------------------------------------------------------------
Previous Topic: how do execute this dynamically
Next Topic: simple question to answer
Goto Forum:
  


Current Time: Wed Oct 28 00:31:16 CDT 2020