Oracle/PL/SQL

Advertisement


Question
Hello

10. If i am inserting row by loop and got any error while inserting than only error records insert into log table and commit and all other records should be rollback. How?

Thanks,
Gaurav

Answer
Hello Gaurav,

You can achieve this by using AUTONOMOUS_TRANSACTION. Autonomous transactions will only commit the data for which procedure autonomous_transaction pragma is given and commit is given in that procedure.

Try your pl/sql block as follows -

declare
...
...
begin
...
for i in 1 .. 10 loop
   begin
     ...
     ...
     insert into tmp_table values (.....);
   exception when others then
      log_errors;
   end;
end loop;
end;

When you come out of this block, none of the records will be committed, except the error log. You will be able to commit or rollback the changes for other records, as commit is not given in the above pl/sql block.

log_errors procedure can be created as follows -

CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO error_logs (id, log_timestamp, error_message)
 VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
 COMMIT;
END;
/

Hope this helps.

Regards

Suchitra

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Suchitra Joshi

Expertise

I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.

Experience

15+ years of Oracle PL/SQL Development

Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 About.com. All rights reserved.