Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Best approach for inserting data into error table

User_4LC0GJun 8 2021

Hello Experts,
Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
As part of my package-function, I am inserting data into a table (used for error logging).
Error_Log table structure

create table error_log (id number, error_desc varchar2(255));

My code

Function fun1
return boolean
is
   -- variables / cursors performing validations

l_dummy varchar2(1);

cursor c1 (P_id number)
is
  select 'X' from tab1 where id = P_ID;

cursor c2 (P_id number)
is
  select 'X' from tab2 where id = P_ID;

begin
        open c1;
        fetch c1 into l_dummy;
        if c1%FOUND
        then
             insert into error_log (id,error_desc) values (1,'ERROR_1');
        end if;
        close c1;


        open c2;
        fetch c2 into l_dummy;
        if c2%FOUND
        then
            insert into error_log (id,error_desc) values (2,'ERROR_2');
        end if;
        close c2;
.
.
.
.
.
.

COMMIT;
return TRUE;

exception
     /* some code to handle exceptions */
return false;
end fun1;

Here I observe that I end up writing multiple insert statements for logging the error into the error_log table, leading to repeated INSERT statements.
Instead, I feel that this can be reduced by writing a separate function which would be called each time i wish to insert a record in the error_log table. Something like the below function:

function error_logging (I_id in number, I_error in varchar2)
return boolean
is
begin
    insert into error_log (id,error_desc) values (I_id,I_error);
return TRUE;

exception
     /* some code to handle exceptions */
return FALSE;
end error_logging;

Could you please suggest the best way forward considering performance.

Thank You

Comments
Post Details
Added on Jun 8 2021
4 comments
1,437 views