variable concat. [message #36718] |
Fri, 14 December 2001 05:11 |
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 |
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 |
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
----------------------------------------------------------------------
|
|
|