CODEWITHSUNDEEP
oracle-databaseoracle11g
F0cus
F0cus

Reputation: 625

Oracle Function - returns the line of code throws error

I know 

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

will return the line number , where the error occurred.

is there any way to get the actual code statement which give the error in Oracle 11g?

Upvotes: 2

Views: 57

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

There isn't a similar mechanism to get the source code automatically.

You could, in principal, get the source code for that line (and maybe surrounding lines) from the user_source or all_source data dictionary views.

The scenario you've described, though, is that you don't have access to the source on the database that is throwing the initial exception. The calling PL/SQL block that currently catches and formats the exception can only see the remote source if the database link user has been granted privileges to see the code in that database, and you've implied it doesn't. You can verify by querying all_source@db_link for the owner and package/procedure in the stack trace. And always try asking for it if you don't ave it already - the source may well not be public on purpose, of course.

If you did have that access you could look at the source manually over that link. You could also query that within your local exception handler, and - as a debugging tool - output that, say with dbms_output. That's then no different to looking at local code, you just have the DB link in the query. But exposing that remote code to whoever calls your local procedure might not be sensible.

Upvotes: 4

Related Questions