Skip to Main Content
  • Questions
  • Line Level Logging using controlled FND_LOG\DBMS_OUTPUT statements vs DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Shantanu.

Asked: July 18, 2018 - 8:03 am UTC

Last updated: July 23, 2018 - 11:44 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,
I have used DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for understanding the exact line where error occurred in a package which is exposed using a service outside.
However one of my client feels I should write diagnostic message after certain set of lines to understand the code where it fails.
I feels this reduces the performance considering that the if loop would still be executed to understand whether to put diagnostics message or not.

In the provide Live SQL :
there are 2 blocks

first_block_example -- code format recommended by Code Reviewer using simple log messages
second_block_example -- code format developed by me , using dbms_utility.format_error_backtrace

with LiveSQL Test Case:

and Chris said...

You're both right! (how's that for fence sitting? ;)

You should instrument your code extensively. That is, have logging before/after almost every statement. This makes your code much easier to debug.

BUT!

Such fine-grained logging often slows your application down too much in production.

So the common practice is to log at different levels, such as:

- DEBUG
- INFO
- WARNING
- ERROR

Make this configurable, so you can choose how much logging to capture for each environment. In development, this will usually be everything. In production just errors & warnings.

The error logs will capture the backtrace information so you can see where it happened. If you capture these errors in production, you may need to get more details to resolve the issue. In this case, temporarily increase the logging level to get what you need. Then switch it back to errors/warnings only once you're done.

Logger is a great open source utility to help you with this. I recommend you use it:

https://github.com/OraOpenSource/Logger

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Incredible resource

Shantanu Dhumal, July 20, 2018 - 12:15 pm UTC

Hi Chris,
Thanks for the response (All that fence sitting? ohh that hurts), that was really a valuable piece of information and helpful.I have downloaded the logger utility.
I looked up through (OraOpenSource/Logger) code and found this little piece of code that is doing some magical stuff :

1436 $if $$no_op $then
1437 null;
1438 $else
1439 if ok_to_log(logger.g_debug) then

I gather this is on-conditional compile if loop, but I do not have much idea about it ,
below are the followup questions ,would appreciate your response on this:
1. Are on-compile functions interpreted\compiled once \ per session \ per alter-compile DDL statement executed for the function.
2. how do we change the value for $$no_op?
3. Once the value for $$no_op\substituted field is changed , do we need to recompile the code back to record the logs.
4. if $$no_op is false , then a normal (runtime) if loop will be executed everytime , this procedure is called. would that not bring the same iterative nature of processing control statements into existence?
Chris Saxon
July 23, 2018 - 11:44 am UTC

The $ sections relate to conditional compilation. They don't become part of the compiled source code. Only the branch that is true.

So in:

1436 $if $$no_op $then 
1437 null; 
1438 $else 
1439 if ok_to_log(logger.g_debug) then 


If $$no_op is true when you compile, then the final code is just "null;". Otherwise it includes "if ok_to_log(...".

These are part of the compile time settings. You can only change these by recompiling the package. For example:

alter package logger compile body PLSQL_CCFLAGS='NO_OP:TRUE';


or to make it false:

alter package logger compile body PLSQL_CCFLAGS='NO_OP:FALSE';


You can read more about it at https://blogs.oracle.com/oraclemagazine/on-conditional-compilation

4. Not sure what you're asking here?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library