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