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?
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 -
for i in 1 .. 10 loop
insert into tmp_table values (.....);
exception when others then
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
INSERT INTO error_logs (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
Hope this helps.