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