Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Isolating the Region with the Error

Sometimes it is difficult to determine which region is having an issue.  Even more difficult can be determining which page process is causing a SQL error.  To track down problems like this, it is common to set conditional display or conditional processing to a condition type of NEVER.  This can either be done one-by-one or several at a time until the page runs correctly.  At least then it is possible know where to focus debugging efforts.

SQL Trace

This is the ability for HTML DB to produce a trace file.  Trace files can help identify where performance problems exist.  Whenever a new or inexperienced developer is facing a performance problem, this feature can be used to produce a trace file, which can be shared with a DBA for analysis.  For those experienced with the tracing sessions and using tkprof, it should be a relief to know it is very easy in HTML DB to produce a trace file.  It is as simple as appending the proper information to the URL.  The syntax to add to the end of the URL is shown below in bold (&p_trace=YES).

f?p=102:3:7646151020298818363::NO::P3_ID:28&p_trace=YES

The trace file will be written to the location identified by the Oracle parameter user_dump_dest.  The trace file produced can be converted to a readable format using the tkprof program, which is provided by Oracle, and provides results similar to the following:

SELECT "ID","LAST_NAME","FIRST_NAME","EMAIL","PHONE",TO_CHAR("RSVP_DATE",
  'MM/DD/YYYY'),"COMPANY","DONATION","PAYMENT"
FROM
 "EASYHDB"."CONFERENCE_RSVP" WHERE "ID" = :B1  

call    count      cpu    elapsed       disk      query    current        rows
------  -----  ------- ---------- ---------- ---------- ----------  ----------
Parse      1      0.00       0.00          0          0          0           0
Execute    1      0.01       0.00          0          0          0           0
Fetch      1      0.00       0.00          0          2          0           1
------- -----  ------- ---------- ---------- ---------- ----------  ----------
total      3      0.01       0.00          0          2          0           1

WWV_FLOW.DEBUG

The wwv_flow.debug can be used to display text information in an application page when the Debug argument of the command link is set to YES.  This is a very valuable way for checking the session state of a page item during different processing points.  Using the WWV_FLOW_DEBUG procedure in the PL/SQL from above reveals the following:

declare
   n_count number;
begin
   select count(*) into n_count from all_objects;
   wwv_flow.debug( '<b>Finished with really bad query.</b>' );
end;

On the application, it will be displayed when the page is rendered in debug mode.

Where this procedure can be extremely valuable is in a stored procedure within the database.  There are times when a developer would want to know what the stored procedure is doing as well as having the ability to display it in an application is a great feature.  A question posted on the HTML DB forum asked about being able to display the SQL statement being executed onto the application page.  This can be done by including code similar to what follows in the stored procedure.

WWV_FLOW.SHOW_ERROR_MESSAGE

The WWV_FLOW.SHOW_ERROR_MESSAGE procedure will redirect the flow of the application to the error page and display the information provided in the arguments.  This is intended for use in Page Processing processes or stored procedures being called during page processing, not page rendering.  A sample procedure call is shown here.

wwv_flow.show_error_message (
   p_message  => 'The customer you were searching for does not
exist.',
   p_footer   => '<b>Customer not found.</b>',
   p_query    => 'select dummy from dual;' );

The results will be displayed on an error page as shown in Figure 17.2.

The value sent in the p_query argument from above is displayed inline as a comment in the HTML source for the page.  Viewing the source in the browser, View à Source menu, reveals the SQL statement.  It also shows the last query that executed from HTML DB giving a better idea of where to look for the problem.

<!--Application:"103" Page:"2701" User:"EASYDEV" -->
<!--request: "SUBMIT"-->
<!--command:select dummy from dual;-->
<!--lastQuery (1st 1000 chars):begin begin
   test_debug;
end;
 end;-->

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.