Is there a function to get the content of all variables in case of an exception as List of Strings

Hello,

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:

exception

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));

raise;

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.

Related:

Leave a Reply