sorry to trouble you again on this.
In one of your articles below
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4951966319022 <code>
You have adviced that frequent commits is not recommended.
Why are you then recommending commit after every record?
Maybe i am not conveying properly what i want to do. I have 2 pseudo codes below.
which of the approaches you recommend
Option1 Pseudo Code ( without committing after every record
---
declare
counter NUMBER:=0;
l_batch_id NUMBER;
i NUMBER:=0;
err_count NUMBER:=0;
tot NUMBER:=0;
x_return_status VARCHAR2(1):=null;
CURSOR get_primary_key_id_csr IS
SELECT primary_key_id
FROM xx_ask_tom_pj
WHERE status='R';
begin
SELECT xx_ask_tom_pj_S.nextval INTO l_batch_id FROM dual;
dbms_output.put_line('start' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line( 'batch id' || l_batch_id );
for x in get_primary_key_id_csr loop
x_return_status := 'S';
l_primary_key_id:=x.primary_key_id;
savepoint xxx;
begin
-- say the routine below is a standard Oracle APPS public API published by Oracle Applications
-- this routuine could be a complex routine that involves validtaions and inserts into several tables
-- say for example creating an Instance in Oracle Install base using csi_item_instances_pub.create_item_instance
csi_item_instances_pub.create_item_instance( );
exception
when others then
x_return_status := 'U';
end ;
-- explicitly erroring for some records to do a test on rollback and prgma autonomous procedure
if i=2 or i=4 or i=8 or i =12 or i=22 or i=24 or i=32 or i =42 or i=52 or i=62 or i=72 or i=82 or i=92 or i=112 or i=114 or i=118 or i =112 or i=122 or i=124 or i=132 or i =142 or i=152 or i=162 or i=172 or i=182 or i=192 then
x_return_status := 'U';
end if;
IF x_return_status = 'U' THEN
-- log the errror through an pragma autonomous procedure and update status'
err_count:=err_count+1;
rollback to xxx;
Write_apiErrOR -- this is a pragma autonomous procedure with a commit;
(
p_error_msg => 'Error in Creating Instance'
, p_primary_key_id=> l_p.primary_key_id
, p_sql_code => null
, p_sql_error => SQLERRM
, p_creation_date => sysdate
, p_batch_id => to_char(l_batch_id)
);
ELSE
counter:=counter+1;
update xx_ask_tom_pj
set status='S'
,batch_id=l_batch_id
where primary_key_id= l_p.primary_key_id;
END IF;
i:=i+1;
if i=1000 then
commit;
i:=1;
end if;
tot:=tot+1;
end loop;
commit;
dbms_output.put_line( 'total records processed ' || tot );
dbms_output.put_line( 'total records success ' || counter );
dbms_output.put_line( 'total records fail' || err_count);
dbms_output.put_line('end' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
end;
/
------------------------------
-- Option 2 Psuedo Code with commit after record
declare
counter NUMBER:=0;
l_batch_id NUMBER;
i NUMBER:=0;
err_count NUMBER:=0;
tot NUMBER:=0;
x_return_status VARCHAR2(1):=null;
CURSOR get_primary_key_id_csr IS
SELECT primary_key_id
FROM xx_ask_tom_pj
WHERE status='R';
begin
SELECT xx_ask_tom_pj_S.nextval INTO l_batch_id FROM dual;
dbms_output.put_line('start' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line( 'batch id' || l_batch_id );
for x in get_primary_key_id_csr loop
x_return_status := 'S';
l_primary_key_id:=x.primary_key_id;
begin
-- say the routine below is a standard Oracle APPS public API published by Oracle Applications
-- this routuine could be a complex routine that involves validtaions and inserts into several tables
-- say for example creating an Instance in Oracle Install base using csi_item_instances_pub.create_item_instance
csi_item_instances_pub.create_item_instance( );
exception
when others then
x_return_status := 'U';
end ;
-- explicitly erroring for some records to do a test on rollback and prgma autonomous procedure
if i=2 or i=4 or i=8 or i =12 or i=22 or i=24 or i=32 or i =42 or i=52 or i=62 or i=72 or i=82 or i=92 or i=112 or i=114 or i=118 or i =112 or i=122 or i=124 or i=132 or i =142 or i=152 or i=162 or i=172 or i=182 or i=192 then
x_return_status := 'U';
end if;
IF x_return_status = 'U' THEN
-- log the errror to an pragma autonomous transaction'
err_count:=err_count+1;
Write_apiErrOR -- this is a regular procedure to the log error and update status column
(
p_error_msg => 'Error in Creating Instance'
, p_primary_key_id=> l_p.primary_key_id
, p_sql_code => null
, p_sql_error => SQLERRM
, p_creation_date => sysdate
, p_batch_id => to_char(l_batch_id)
);
ELSE
counter:=counter+1;
update xx_ask_tom_pj
set status='S'
,batch_id=l_batch_id
where primary_key_id= l_p.primary_key_id;
END IF;
i:=i+1;
commit;
tot:=tot+1;
end loop;
dbms_output.put_line( 'total records processed ' || tot );
dbms_output.put_line( 'total records success ' || counter );
dbms_output.put_line( 'total records fail' || err_count);
dbms_output.put_line('end' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
end;
/
-------------