Home » SQL & PL/SQL » SQL & PL/SQL » variable concat.
variable concat. [message #36718] Fri, 14 December 2001 05:11 Go to next message
Patty
Messages: 3
Registered: November 2001
Junior Member
Can someone tell me what I need to do here?

I declared three records using %ROWTYPE but I need to increment the ending number up on each pass for the record. I know I could declare variable for each one but there are 20 elements and I thought it might save typing this way. Any suggestions?
OPEN cur_mission_summary_cart;
FETCH cur_mission_summary_cart
INTO (cur_mission_summary_cart_rec||v_ctr);

----------------------------------------------------------------------
Re: variable concat. [message #36720 is a reply to message #36718] Fri, 14 December 2001 06:01 Go to previous messageGo to next message
SAlapati
Messages: 12
Registered: November 2001
Junior Member
You will get an error if you do
cur_mission_summary_cart_rec||v_ctr

You must concat counter to each element of the record like
cur_mission_summary_cart_rec.element1 || v_ctr

Just use %ROWCOUNT

Every time you do a fetch the Rowcount is incremented for you.. thus preventing unnecessary declaration of temp variables.

FFETCH cur_mission_summary_cart
INTO (cur_mission_summary_cart_rec.Element1 || cur_mission_summary_cart%ROWCOUNT|);

here is an example I used with dba_constraints
declare
cursor c_cur is
select CONSTRAINT_NAME, CONSTRAINT_TYPE
from dba_constraints
where rownum < 5 ;
c_rec c_cur%ROWTYPE ;
begin
open c_cur ;
loop
fetch c_cur into c_rec ;
exit when c_cur%NOTFOUND ;
dbms_output.put_line(c_rec.constraint_name || '= ' || c_cur%ROWCOUNT );
end loop;
close c_cur ;
end ;
/

SYS_C0074= 1
SYS_C0075= 2
SYS_C0076= 3
SYS_C0077= 4

hope this helps
SAlapati

----------------------------------------------------------------------
Re: variable concat. [message #36726 is a reply to message #36718] Fri, 14 December 2001 06:41 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you are not in right direction. use either of following 2 methods.

1) declare record type and fetch values into that record.

eg:

declare
type r1 is record (n number,y number);
r2 r1;
cursor c1 is select 1,2 from dual;
begin
open c1;
loop
fetch c1 into r2;
exit when c1%notfound;
dbms_output.put_line(r2.n||' '||r2.y);
end loop;
end;

2) use for loop, so that you dont need to worry about fetch statement.oracle declares record type internally when you use for loop

declare
cursor c1 is select empno,ename from emp;
begin
for crec in c1 loop
dbms_output.put_line(crec.empno||' '||crec.ename);
end loop;
end;

SURESH

----------------------------------------------------------------------
Previous Topic: What is "i" in oracle 8i/9i
Next Topic: pl/sql-urgent
Goto Forum:
  


Current Time: Thu Mar 28 13:55:08 CDT 2024