UPDATE and commit;

[Subha]

create or replace PROCEDURE update_testing is
cursor c1 is select a.EMP_ID val_id from LOCATION A ,TEST B WHERE a.REL_DIRECTORY=b.REL_DIRECTORY;
my_counter number(12);
begin
my_counter:=0;
for c2 in c1 loop
UPDATE TEST SET EMP_ID=C2.VAL_ID;
my_counter:=my_counter+1;
if my_counter=50000 then
commit;
my_counter:=0 ;
end if;
COMMIT;
end loop;
end;

This procedure when executed does not not commit after every 50000 records and the procedure fails because of rollback segs ... What is wrong in the procedure ..

[pangul]

i can't see any reason for not doing what you are trying to do i.e commit afer every 50000.

check to see if you are getting back the right result by using dbms_output package after every 10000 records also put some otput statement in if block to check if that block is executed. if that block is executing then there is problem with the records fetched .

[jmodic]

Subha,

I do not want to be rude, but your procedure is a MESS. Among other things:

1. You have two commits in it: one that should fire aftere every 50000 updates and another one that fires after *every* update! I suppose you only want to keep the first one and remove the second one.

2. For each record that you read from cursor c1 you perform an update of *all records* in table TEST with the same value VAL_ID. I think you have forgotten to add some WHERE condition in your update clause. As it is now, if you have 500.000 records in test and if your cursor c1 returns 100.000 you are updating 500.000 records 100.000 times. No wonder you are getting some rollback segment error.

You are not saying what error are you getting, but I'm sure it is ORA-1555 "snapshot to old". Now even if you remove your second commit you are pretty good chances you will still get this error, because you are actualy doing "fetch accros commit". There are many threads in this forum explaining the reasons for ORA-1555 and how to avouid them - use the forum's search facility and read those threads.

[hbk_emp]

the above explaination is exactly what I think,
set your arraysize to less and increase your sort_area_size and add a rbs a temp on and then drop it.

[chrisrlong]

Post superceded by...

http://www.dbasupport.com/forums/sho...threadid=12883

- Chris

[sambavan]

Waaaw, what a confusion.

Chris, I'm opening this thread as it would make sense to continue on this one rather than the other. If there is a way to merge these two threads it would be really nice.



Sam

[Edited by sambavan on 07-06-2001 at 11:13 AM]

[chrisrlong]

hehe - No way of combining that I know of. We could just copy the other post into here and close *that* thread maybe. - Watcha think?

- Chris

[sambavan]

: That would be a good idea. Would you go ahead and do it?



Sam

[chrisrlong]

Alrighty then

Here is some more (new?) information from Subha:

SQL> desc location
Name Null? Type
----------------------------------------- -------- -----------------
LOCATION_ID NUMBER(5)
REL_DIRECTORY VARCHAR2(100)

location table has 1200o rows
SQL> desc TEST
Name Null? Type
----------------------------------------- -------- ------------------
BATCH_NAME NUMBER(10)
REL_DIRECTORY VARCHAR2(100)
FILENAME VARCHAR2(50)
REL_DATE DATE
ACC_NUMBER VARCHAR2(26)
DOC_TYPE VARCHAR2(5)
CUST_NAME VARCHAR2(30)
VIN VARCHAR2(20)
C_CODE VARCHAR2(6)
BRANCH_CODE VARCHAR2(8)
IMAGE_TYPE VARCHAR2(2)
ZIP_DATE DATE
SENT_DATE DATE
LOCATION_ID NUMBER(5)

table test has 26 million rows...

update TEST a set a.location_id=(select b.location_id FROM location b WHERE a.rel_directory=b.rel_directory)

this above update works in my development database ...without this procedure ...




create or replace PROCEDURE update_testing is
cursor c1 is select a.EMP_ID val_id from LOCATION A ,TEST B WHERE a.REL_DIRECTORY=b.REL_DIRECTORY;
my_counter number(12);
begin
my_counter:=0;
for c2 in c1 loop
UPDATE TEST SET EMP_ID=C2.VAL_ID;
my_counter:=my_counter+1;
if my_counter=50000 then
my_counter:=0 ;
end if;
COMMIT;
end loop;
end;

This procedure is not working and the results are not correct...
pls correct this procedure

The cursor and the update should commit every 50000 rows .

This i want as urgent as possible

[Subha]

Sambavan can you correct the procedure wheer I am wrong ..