Skip to Content

A useful way to debug SQLscript code is to add TRACE statements. When you add TRACE statements to SQLscript code the trace results are stored in temp tables. The temp table names can be retrieved from table SYS.SQLSCRIPT_TRACE.

If like me you use this alot to debug then you may find it tedious to select from the SYS.SQLSCRIPT_TRACE table each time to get the temp table for the table variable output and then select from this temp table.

I meant to put this up a while back but I created a quick and dirty procedure to display the results of all the trace statements quickly. You may find it useful when analyzing table variable data.


CREATE PROCEDURE TRACE_RESULTS_P(IN result_type NVARCHAR(10), IN filter_criteria NVARCHAR(200))
LANGUAGE SQLSCRIPT AS
  CURSOR c_trace FOR select statement_id, variable_name, table_name, trace_timestamp
  from SYS.SQLSCRIPT_TRACE order by trace_timestamp desc;
  sql_string NVARCHAR(20000) := '';
  trace_count INT := 0;
BEGIN
  if result_type = 'count'
  THEN
  FOR trace_row AS c_trace
  DO
  IF :trace_count > 0
  THEN
  sql_string := :sql_string || ' UNION ';
  END IF;
  sql_string := :sql_string|| ' select '''||trace_row.variable_name||''' as trace_name, '''||trace_row.trace_timestamp||
  ''' as timestamp, count(*) as count from '||trace_row.table_name||' '||filter_criteria;
  trace_count := trace_count+1;
  END FOR;
  EXECUTE IMMEDIATE (:sql_string);
  ELSEIF result_type = 'all'
  THEN
  FOR trace_row AS c_trace
  DO
  sql_string := 'select '''||trace_row.variable_name|| ''' as trace_name, * from ';
  sql_string := :sql_string || trace_row.table_name||' '||filter_criteria;
  EXECUTE IMMEDIATE (:sql_string);
  END FOR;
  END IF;
END;


As you can see it takes two params:

– result_type ~ specifies whether to just get the count of each table variable or to display the contents of the table variables

– filter_criteria ~ specifies some filter criteria to be applied to the query on the table variables (useful if looking for results on one document number for example)

It loops through the temp tables and uses dynamic sql to execute the selects on the tables.

To demonstrate I created a small procedure with a couple of trace statements:


CREATE PROCEDURE TRACE_TEST_P(OUT t_vbap ecc_reporting.vbap)
LANGUAGE SQLSCRIPT AS
BEGIN
  t_vbak = select vbeln from ecc_reporting.vbak where vbeln IN ('0030059722','0030136383');
  t_vbak = trace(:t_vbak);
  t_vbap = select vbap.*  from ecc_reporting.vbap as vbap, :t_vbak as vbak
  where vbap.vbeln = vbak.vbeln;
  t_vbap = trace(:t_vbap);
END;


After calling the procedure there are two rows in the SYS.SQLSCRIPT_TRACE for the two table variables which were traced:

SQLSCRIPT_TRACE.PNG

So now by calling my TRACE_RESULTS_P procedure I can quickly display the data in the temp tables:


call TRACE_RESULTS_P('all','');

trace_all_1.PNG

TRACE_RESULTS_P

trace_all_2.PNG

With filter criteria:


call TRACE_RESULTS_P('all',' where vbeln = ''0030059722''');

trace_all_filter_1.PNG

trace_all_filter_2.PNG

Getting the count:


call TRACE_RESULTS_P('count','');

trace_count.PNG

Count with filter criteria:


call TRACE_RESULTS_P('count',' where vbeln = ''0030059722''');

trace_count_filter.PNG

The above can be very handy if you have trace statements on multiple table variables.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. John Appleby

    As a point of interest, why do you use this rather than the SQLScript debugger? As of SPS06 and especially in SPS07, the debugger is quite sophisticated. Do you still find these TRACE statements useful?

    (0) 
    1. Peter Murphy Post author

      Hi John, for sure the debugger has improved considerably. We’ve recently upgraded non-prod systems to SPS07 and I’m particularly happy to see the introduction of Catalog schema debugging. Will be even better when step over, step into, etc.. is introduced in future releases.

      I wouldn’t say it’s an either/or situation in terms of preference of trace over debugger for me as I use the debugger alot also but I do find trace statements useful and would say they still have their place even with the improvements with the debugger for a few reasons:

      –  In my piece above I give an example of filtering on a sales order – I find this handy debugging a particular report of ours where we have a large procedure which reports on 10s of thousands of orders and has some really complex filtering logic. It can be handy to be able quickly filter down on the trace temp tables for a specific order instead of filtering each debug variable individually. More of a speed of debugging thing really.

      – Once you don’t truncate the temp tables it can be a handy way of doing a before and after comparison of your data sets after you have applied a code change.

      – Also as you are developing code it can be useful to use the data in the trace temp tables in developing queries in the SQL editor for use in subsequent statements of the procedure.

      Peter

      (0) 

Leave a Reply