Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Feedback based on https://www.insum.ca/feuertip-7-how-not-to-handle-dml-errors/
create or replace procedure full_of_dml (
p_order in number,
p_lines in lines_t
)
is
l_scope logger_logs.scope%type := 'full_of_dml';
l_params logger.tab_param;
begin
logger.append_param(l_params, 'p_order', p_order);
update my_orders
set status = 2
where order_id = p_order_id;
for indx in 1..p_lines.count
loop
-- MD: Don't do this it'll create a lot of entries in an array.
-- MD: In this example not bad but will take up more memory if this is scattered in all your code
-- logger.append_param (
-- l_params, 'p_lines ' || indx, p_lines(indx));
-- MD: Instead do this: Good for debugging but won't show up when an exception happens and
-- MD: Logger is just set to log errors
logger.log('indx: ' || indx, l_scope);
logger.log('p_lines(indx): ' || p_lines(indx), l_scope);
insert into my_line_items (
order_id,
line_num,
line_info )
values (
p_order,
indx,
p_lines(indx)
);
end loop;
exception
when others
then
-- MD: Side note: I usually don't put commits or rollbacks as it should be the calling app's determiniation as to what to do
-- rollback;
-- MD: at this point can do 2 things. Either add key metrics to be logged to l_params or just log them:
logger.log_error('indx: ' || indx, l_scope);
logger.log_error('p_lines(indx): ' || p_lines(indx), l_scope);
-- MD: OR
logger.append_param(l_params, 'indx', indx);
logger.append_param(l_params, 'p_lines(indx)', p_lines(indx));
logger.log_error('Unhandled exception', l_scope, l_params);
-- MD: Based on logic / commend for rollback, should raise the error and let the calling app determine what to do
raise;
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment