Forum Stats

  • 3,874,589 Users
  • 2,266,749 Discussions
  • 7,911,905 Comments

Discussions

Best approach for inserting data into error table

User_4LC0G
User_4LC0G Member Posts: 13 Green Ribbon

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

Tagged:

Answers