| oracle 92 [message #63844] |
Tue, 16 November 2004 05:36  |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
The Below Code Works greate in Oracle 8.1.7, not any more in 9.2
******* Code ***********
declare
a integer;
u integer :=0;
i integer :=0;
b number(20);
result_str varchar2(150);
BEGIN
FOR temp_cur in (select * from tst1) LOOP
b:= temp_cur.INFO_SEQ_NUM;
EXIT WHEN SQL%NOTFOUND;
select count(1) into a from dual where exists (
select null FROM tst where tst.INFO_SEQ_NUM = temp_cur.INFO_SEQ_NUM);
If a > 0 then
UPDATE tst SET
INFO_RPT_DT=temp_cur.INFO_RPT_DT,
INFO_SEQ_NUM=temp_cur.INFO_SEQ_NUM,
TABLE_NM=temp_cur.TABLE_NM
where
INFO_SEQ_NUM=temp_cur.INFO_SEQ_NUM;
u := u+ 1;
commit;
elsif a!=1 and temp_cur.INFO_SEQ_NUM is not null then
insert into tst values(temp_cur.INFO_RPT_DT, temp_cur.INFO_SEQ_NUM, temp_cur.TABLE_NM);
commit;
i := i + 1;
end if;
end loop;
dbms_output.put_line('Number of Rows Updated are : ' || u || chr(10) || 'Number of Rows inserted: ' || i);
end;
/
******** end ****************
BUT, if remove the "COMMIT" statements from inside the Loop and have one COMMIT after the of the Loop, like as follows
declare
a integer;
u integer :=0;
i integer :=0;
b number(20);
result_str varchar2(150);
BEGIN
FOR temp_cur in (select * from tst1) LOOP
b:= temp_cur.INFO_SEQ_NUM;
EXIT WHEN SQL%NOTFOUND;
select count(1) into a from dual where exists (
select null FROM tst where tst.INFO_SEQ_NUM = temp_cur.INFO_SEQ_NUM);
If a > 0 then
UPDATE tst SET
INFO_RPT_DT=temp_cur.INFO_RPT_DT,
INFO_SEQ_NUM=temp_cur.INFO_SEQ_NUM,
TABLE_NM=temp_cur.TABLE_NM
where
INFO_SEQ_NUM=temp_cur.INFO_SEQ_NUM;
u := u+ 1;
-- commit;
elsif a!=1 and temp_cur.INFO_SEQ_NUM is not null then
insert into tst values(temp_cur.INFO_RPT_DT, temp_cur.INFO_SEQ_NUM, temp_cur.TABLE_NM);
-- commit;
i := i + 1;
end if;
end loop;
commit;
dbms_output.put_line('Number of Rows Updated are : ' || u || chr(10) || 'Number of Rows inserted: ' || i);
end;
Works greate!!! in 9.2, why? can anyone help me out what the new feature that is causing it to run like this.
Thanks
|
|
|
|
|
|
| Re: oracle 92 [message #63846 is a reply to message #63845] |
Tue, 16 November 2004 06:13  |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
yes, I know having commits inside the loop is no good. but it always worked greate in 817, but in 9205, it just loops through one record and exits out of the loop. in 817, it use to loop through all the records exits out of the loop. so any idea why? and what is causing it?
thanks
|
|
|
|
| Re: oracle 92 [message #63850 is a reply to message #63844] |
Tue, 16 November 2004 20:10 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Can't really understand what would cause the difference in 8i / 9iR2, but your EXIT when condition doesn't make sense. A cursor-for loop automatically ends when there are no records left to be fetched.
(better approach if there is a unique index on tst:
for r in (select * from tst1)
loop
begin
if r.seq_no is not null
then
insert into tst (...)
end if;
exception
when dupval_on_index
then
update tst ....
end;
end loop;
hth
|
|
|
|
| Re: oracle 92 [message #63855 is a reply to message #63850] |
Wed, 17 November 2004 03:14 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
yes, i understand the exit doens't make any sense. but developers says it was working in 8i but not in 9i, why?.
You are correct after removing and EXIT WHEN SQL%NOTFOUND; line from the code and leaving the commits inside the loop, it works greate in 9i. I dont know why it worked in 8i with EXIT WHEN SQL%NOTFOUND; statement in it.
Thanks a lot.
|
|
|
|
| Re: oracle 92 [message #63858 is a reply to message #63855] |
Wed, 17 November 2004 04:34 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Don't really understand why it didn't work. Although it was kinda sloppy I think it should have worked.
But now it works, so why worry ;)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
