1  TRACE_Control
   Writes values to the trace log file after the trace extended
   debug flag is set. The TRACE control statement lets you specify
   multiple value expressions. It stores a value in a log file for
   each value expression it evaluates.

   Trace logging can help you debug complex multistatement
   procedures.
 

2  Environment
   You can use the TRACE control statement in a compound statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0trace-statement =                    
                                      
 qqqq> TRACE qqwq> value-expr qqwqq>  
               mqqqqq , <qqqqqqqj     

 

2  Arguments
 

3  value-expr

   Specifies a symbol or string of symbols used to represent or
   calculate a single value.

   See the Value_Expressions HELP topic for a complete description
   of the variety of value expressions that SQL provides.
 

2  Examples
   Example 1: Tracing a multistatement procedure

   SQL> ATTACH 'FILENAME MF_PERSONNEL';
   SQL> SET FLAGS 'TRACE';
   SQL> DECLARE :i INTEGER;
   SQL> BEGIN
   cont>    WHILE :i <= 10
   cont>     LOOP
   cont>         TRACE ':i is', :i;
   cont>         SET :i = :i +1;
   cont>     END LOOP;
   cont> END;
   ~Xt: :i is 0
   ~Xt: :i is 1
   ~Xt: :i is 2
   ~Xt: :i is 3
   ~Xt: :i is 4
   ~Xt: :i is 5
   ~Xt: :i is 6
   ~Xt: :i is 7
   ~Xt: :i is 8
   ~Xt: :i is 9
   ~Xt: :i is 10

   Example 2: Generating a query outline when the TRACE statement is
   disabled

   SQL> DECLARE :LN CHAR(40);
   SQL> SET FLAGS 'NOTRACE';
   SQL> BEGIN
   cont> TRACE 'Jobs Held: ',
   cont>     (SELECT COUNT(*)
   cont>         FROM JOB_HISTORY
   cont>         WHERE EMPLOYEE_ID = '00201');
   cont> SELECT LAST_NAME
   cont>     INTO :LN
   cont>     FROM EMPLOYEES
   cont>     WHERE EMPLOYEE_ID = '00201';
   cont> END;
   -- Oracle Rdb Generated Outline :
   create outline QO_A17FA4B41EF1A68B_00000000
   id 'A17FA4B41EF1A68B966C1A0B083BFDD4'
   mode 0
   as (
     query (
   -- Select
       subquery (
         EMPLOYEES 0     access path index       EMPLOYEES_HASH
         )
       )
     )
   compliance optional     ;
   SQL>

   If the query outline is generated with TRACE enabled, then
   two queries appear; the first is for the subquery in the TRACE
   statement and the other is for the singleton SELECT statement.

   If this second query outline is used at run time with the TRACE
   statement disabled, then it cannot be applied to the query.
   Because the outline was created with compliance optional, the
   query outline is abandoned and a new strategy is calculated. If
   compliance is mandatory, then the query fails. See Example 3.

   SQL> DECLARE :LN CHAR(40);
   SQL> SET FLAGS 'TRACE';
   SQL> BEGIN
   cont> TRACE 'Jobs Held: ',
   cont>     (SELECT COUNT(*)
   cont>         FROM JOB_HISTORY
   cont>         WHERE EMPLOYEE_ID = '00201');
   cont> SELECT LAST_NAME
   cont>     INTO :LN
   cont>     FROM EMPLOYEES
   cont>     WHERE EMPLOYEE_ID = '00201';
   cont> END;
   -- Oracle Rdb Generated Outline :
   create outline QO_A17FA4B41EF1A68B_00000000
   id 'A17FA4B41EF1A68B966C1A0B083BFDD4'
   mode 0
   as (
     query (
   -- Trace
       subquery (
         JOB_HISTORY 0   access path index       JOB_HISTORY_HASH
         )
       )
     query (
   -- Select
       subquery (
         EMPLOYEES 0     access path index       EMPLOYEES_HASH
         )
       )
     )
   compliance optional     ;
   ~Xt: Jobs Held: 4
   SQL>

   Example 3: Using an Outline with Tracing Enabled That Was Created
   with Tracing Disabled

   This example shows that enabling the TRACE statement may affect
   query outlines defined when TRACE was disabled.

   SQL> DECLARE :LN CHAR(40);
   SQL>
   SQL> BEGIN
   cont> TRACE 'Jobs Held: ',
   cont>     (SELECT COUNT(*)
   cont>         FROM JOB_HISTORY
   cont>         WHERE EMPLOYEE_ID = '00201');
   cont> SELECT LAST_NAME
   cont>     INTO :LN
   cont>     FROM EMPLOYEES
   cont>     WHERE EMPLOYEE_ID = '00201';
   cont> END;
   ~S: Outline QO_A17FA4B41EF1A68B_00000000 used
   ~S: Outline/query mismatch; assuming JOB_HISTORY 0 renamed to EMPLOYEES 0
   ~S: Full compliance with the outline was not possible
   Get     Retrieval by index of relation EMPLOYEES
     Index name  EMPLOYEES_HASH [1:1]       Direct lookup