Debug statements in plsql code

Question

I have been working with a lot of legacy plsql code off late. I see debug statements like below all over the code.The 'if' check was added to avoid the overhead of procedure call when debug mode is OFF.

IF (Debug mode is on) THEN
  Debug_pkg.logmsg(l_module_name,'Unexpected error : '|| SQLERRM, FND_LOG.LEVEL_WARNING);
END IF;

Is there a way to write debug statement in one line? In C, we have macros that can be useful to avoid procedural overhead. Can I do something similar in plsql code?

Edit:

Adding a little more details since my question might have confused some. What I am trying to avoid is writing 3 lines to print one debug statement. Can I write debug statement in one line using macros? Reason why I am trying to do this, When such statements are added all over the place it gives rise to clutter and reduces readability. When it should have been just one line, I see 3 lines for each debug statement.

Second edit:

Added my answer below.

can you please edit and move your approach to an answer?

Sathyajith Bhat
– Sathyajith Bhat

2013-02-27 04:46:05 +00:00
Commented Feb 27, 2013 at 4:46 — Sathyajith Bhat
– Sathyajith Bhat, Commented Feb 27, 2013 at 4:46
@Sathya - Added my answer below.

plspl
– plspl

2013-02-27 19:13:50 +00:00
Commented Feb 27, 2013 at 19:13 — plspl
– plspl, Commented Feb 27, 2013 at 19:13

Justin Cave · Accepted Answer · 2013-02-26 22:29:34Z

3

Depending on the version of Oracle you're using, it is possible to use conditional compilation in PL/SQL. Something like

$IF $$debug_mode
$THEN
  Debug_pkg.logmsg(l_module_name,'Unexpected error : '|| SQLERRM, FND_LOG.LEVEL_WARNING);
$END

where $debug_mode is set in the session's PLSQL_CCFLAGS setting or

$IF package_name.debug_mode = 1
$THEN
  Debug_pkg.logmsg(l_module_name,'Unexpected error : '|| SQLERRM, FND_LOG.LEVEL_WARNING);
$END

where package_name.debug_mode is a package-level variable that indicates whether the code should be compiled in debug mode.

Again, depending on the version of Oracle you're using, you may be able to put the debug mode check in debug_pkg.logmsg and rely on the compiler either to automatically determine that it would be beneficial to inline the call (eliminating the procedure call overhead) or to instruct the compiler to inline the call with the INLINE pragma. Here is an article that discusses inlining in PL/SQL in more detail.

edited Feb 26, 2013 at 22:29

answered Feb 26, 2013 at 21:56

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

plspl Over a year ago

Thanks for your examples. What I am trying to do is avoid writing 3 lines to print one debug statement. Your answers seems to enable the compiler to remove the debug statements when not in debug mode.This is not what I am looking for. I am looking for a way for the developer to write just one line to print debug statement when in debug mode.

Justin Cave Over a year ago

@plspl - Correct. If that isn't what you're asking, potentially you're looking for subprogram inlining. Expanded my answer for that.

plspl Over a year ago

Thanks, the link seems to be useful.

plspl Over a year ago

can you edit your answer to remove text related to conditional compilation? I can then accept your answer. I also added an example myself. Thanks for your help.

plspl · Accepted Answer · 2013-02-27 19:12:46Z

0

Thanks for the link http://www.oracle-developer.net/display.php?id=502 from Justin Cave. I ended up doing this for now,

  PROCEDURE debug_msg(txt VARCHAR2) 
  IS 
  BEGIN  
      IF (debug mode is on) THEN
        debug_pkg.logmsg('Module', txt , LEVEL_WARNING);
      END IF;
  END;

To inline the above procedure, you need to do this.

PRAGMA INLINE(debug_msg, 'YES');

However inline is just a request to the compiler, it may or may not be inlined. Also, the procedure being inlined needs to be defined in the same package.

answered Feb 27, 2013 at 19:12

plspl

72813 silver badges26 bronze badges

2 Comments

schurik Over a year ago

I am curious what the "debug mode is on" is? is it a valid pl/sql expression, or is it a pseudo code

plspl Over a year ago

its just pseudo code to indicate a variable/expression that determines if debug mode is on.

Collectives™ on Stack Overflow

Debug statements in plsql code

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related