Thanks for the question, Anastasia.
Asked: October 10, 2016 - 11:44 am UTC
Last updated: October 10, 2016 - 1:48 pm UTC
Version: 12c
Viewed 1000+ times
You Asked
Hello, Tom!
I'm sure, my problem is not nontrivial one, but I've certainly stuck on it and need your help.
This is it.
I need to write a logging procedure that will catch exceptions from other pl-sql blocks and assembly error messages with the structure like this:
-- Date_of_eror (timestamp ) -- timestamp when the error occurred
-- Module_name (varchar2) -- name of the procedure/function that produced the error
-- Param_list (clob) -- the list of in-parameters (and their values) which the procedure was called with.
-- Ora_txt (varchar2) -- sqlerrm.
I tried to find out which one of dbms_*** packages has functions that fit to my needs, but I didn't succeed.
The only way to go, I can think of, is to assembly this Param_list (clob) variable in the start of every procedure and use it as an input parameter in the exception handling section. Something like the folowing:
CREATE OR REPLACE
PROCEDURE Test_procedure(
p_in_num NUMBER,
p_in_var VARCHAR2)
AS
v_params clob;
begin
-- Operation1 --
-- Operation2 --
-- .......... --
-- OperationN --
raise_application_error( - 20000, 'User_ERR') ;
EXCEPTION
when others then
v_params:= 'p_in_num: '||p_in_num||chr(10)||'p_in_var '||p_in_var;
pkg_logging.log_message(
p_in_log_lvl => '3',
p_in_module => 'Test_procedure',
p_in_parameters => v_params,
p_in_sqlerrm => sqlerrm) ;
END;
But I certainly don't like this approach (as wouldn't anyone else, I'm afraid)
1) I wouldn't hardcode procedure name (now it's just assigned into p_in_module parameter)
2) The same for p_in_parameters - it'd be better to get the full list of them from some system view or by means of one of dbms_*** packages, and not to collect it within the procedure's code.
Tell me, please, which Oracle's feature I'm overlooking, and which opportunities are there to transfer in_parameters list from one procedure to another.
Thanks in advance.
and Chris said...
If you want to pass a list of complex values you can create an array of records.
First define a record for the parameter name and value:
type params is record (
parameter_name varchar2(30),
parameter_value varchar2(4000)
);
Then define an array based on this:
type param_arr is table of params index by pls_integer;
Once you've loaded values into the array you can pass it as a single parameter:
declare
type params is record (
parameter_name varchar2(30),
parameter_value varchar2(4000)
);
type param_arr is table of params index by pls_integer;
procedure log (params param_arr) is
begin
for i in 1 .. params.count loop
dbms_output.put_line (
'Parameter: ' || params(i).parameter_name ||
'; Value: ' || params(i).parameter_value
);
end loop;
end log;
procedure p(p1 int, p2 int) is
params param_arr;
begin
params(1).parameter_name := 'p1';
params(1).parameter_value := p1;
params(2).parameter_name := 'p2';
params(2).parameter_value := p2;
log(params);
end p;
begin
p(1, 2);
end;
/
PL/SQL procedure successfully completed.
Parameter: p1; Value: 1
Parameter: p2; Value: 2
Or, instead of building your own framework, you can save yourself time by using one built by someone else!
The open source logger project has already implemented this functionality:
https://github.com/OraOpenSource/Logger I recommend you use it! :)
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment