Toad for Oracle – How To Debug PLSQL code that uses the HTP or HTF packages to build Web applications

When using the debugger in TOAD you are able to set various options, paramters etc in a small anonymous block of PL/SQL. While this is fine for non-web oriented PL/SQL code, it doesn’t work with Web based code written in PL/SQLwhen that code makes calls to the HTP and HTF packages.

However, thanks to Connor MacDonald, a very talented DBA from Perth, Australia, there is a solution, as follows :

Define two variables in the DECLARE part of the block as follows :

vNames  owa.vc_arr;
  vValues owa.vc_arr;

Then add the following code as the first part of the BEGIN – END block.

htp.init;
  vNames(1) := 'REQUEST_PROTOCOL';
  vValues(1)  := 'HTTP';
  owa.init_cgi_env(
      num_params => 1,
      param_name => vNames,
      param_val  => vValues );

And finally, add this one line of code to immediately after the call to the procedure you wish to debug :

owa_util.showpage;

Turn on DBMS_OUTPUT on the appropriate tab in TOAD, and test your procedure. When single stepping, or running, you do not get an ORA-06502 error in OWA_UTIL when you first hit a call to HTP or HTF routines and, when done, the HTML that your code generated is dumped out to the dbms_output tab.

This is not as helpful as running it under Apache, but far better than not being able to debug at all.