I want to improve our exception handling.
In case of an exception it would be good to have not only the errormessage, the errror_backtrace or error_stack.
We work with dynamic sql and for me it would be an improvement if I could output all Input, Output-variables and the content of the dynamic sql-Statement in each exception of the code.
Sure I can seperatelly adress each variable and log them with the data content like in this example:
when others then
cg$errors.log_new (null,null,’Test-Unit’,null,’SQLCODE: ‘ || SQLCODE || chr(10)
|| ‘ — SQLERRM: ‘ || sqlerrm || chr(10)
|| ‘ — Stacktrace: ‘ || dbms_utility.format_error_backtrace || chr(10)
|| ‘ — Parameter:’|| chr(10)
|| ‘vWorkstation in Varchar2: ‘ || vWorkstation || chr(10)
|| ‘vUser in Varchar2: ‘ || vUser || chr(10)
|| ‘vtest clob : ‘ || vtest|| chr(10)
|| ‘vtest1 clob : ‘ || vtest1|| chr(10)
|| ‘vtest1m clob : ‘ || vtest1m|| chr(10)
|| ‘temp_table clob : ‘ || temp_table|| chr(10));
But this is high-maintainance, cause I need to adress different varibles (vWorkstation, vUser, and so on) in each exception.
When I run a PL-SQl- Unit in Debug- Mode I can see a list of all variables (vWorkstation, vUser, and so on) with each step I debug the code. So I think when the sql-developer shows me this data during debugging there must be a way to get a list of the variables during runtime.
So I thought if there is a function or procedure embedded by Oracle, that I can call and which returns all variables used in the code with their names an their content this would be great. I would like to call this function in each of my tougher exceptions.
Do you know such a Oracle function?
Thank you very much.