On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, June 25, 2008

An Easy Way to Debug SQL Queries


This is a separate piece of code that makes it easy to debug another query. When there is a problem with your code, run this from the prompt. In my case, I've cleverly named this file D.SQL. When I type @D and answer 'on,' any query I run next will have echo, headings, verify, timing, feedback and term set to 'on.' Additionally, any columns not being printed (coded with 'noprint') will be displayed.

You use the following variables in your queries:

  • &OnOff For example:
   Set Term &OnOff
Set Echo &OnOff
  • &xPrint For example:
     Column Loc_ID &xPrint
Use these only when you normally want the function Off. If you want Term On at a certain point (i.e., with Accept), don't use this variable.

--+-----------------------------------------------------------------------+
--| Ask the question
--+-----------------------------------------------------------------------+
Set Echo Off
Set Term On

Prompt
Accept OnOff Format A3 Default Off -
Prompt 'Do you want Echo/Feedback/Verify/Timing/Print On or Off? '
Prompt

Set Term Off
--+-----------------------------------------------------------------------+
--| Set everything off so it doesn't show up in this program
--+-----------------------------------------------------------------------+

Set Feedback Off Timing Off Verify Off Heading Off Newpage None Echo off
Ttitle Off
Repheader Off

--+-----------------------------------------------------------------------+
--| Set up 'amper'-xprint to be used in code in place of 'noprint'
--+-----------------------------------------------------------------------+

Column PrintOrNo New_Value xPrint Noprint

Select Decode(Upper('&OnOff'), 'ON' , 'Print' ,
'OFF', 'NoPrint' ,
'NoPrint'
)
As
PrintOrNo
From Dual;
--+-----------------------------------------------------------------------+
--| Set Each Debugging Tool On Or Off As Requested.
--+-----------------------------------------------------------------------+
Set Feedback &Onoff
Set Timing &Onoff
Set Verify &Onoff
Set Echo &Onoff
Set Heading On
Set Term On
See Oracle documentation for further detail:
Accept
Decode
Set Feedback Timing Verify Heading Newpage Echo

No comments:

Post a Comment