[Answered] SQL Trace for Oracle Stored Procedures?

August 13, 2014, 12:21 PM

David Briars

I've been successfully using the following statements to turn on SQL tracing for TABLE FILE commands against Oracle tables and views:

-* Activate the trace facility, and make sure that only the traces we want are
-* activated.
SET TRACEUSER = ON
SET TRACEOFF = ALL
-* Activate Aggregation/Join analysis and Statement tracing.
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
-* Turn off the printing of a timestamp, within the statement trace.
SET TRACESTAMP=OFF
-RUN

When I attempt to trace a TABLE FILE command against an Oracle Package.Stored Procedure, the trace returns no generated SQL.

This message has been edited. Last edited by: David Briars,

Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster

August 14, 2014, 06:22 AM

Rifaz

Hi David,

I've no clue on your issue, though would like to share a post bookmarked recently related to this(not Oracle SP's).

Table Yeilds Error on Query, Not sure why

Check Wep5622's last comments, you might need to look at the source code of HTML if you enable &ECHO.

Thanks,
Rifaz

August 22, 2014, 06:24 PM

David Briars

The IB HelpDesk assisted me with this question.

quote:

Thanks for all of the detail you provided. What you described is actually
expected behavior. A stored procedure is processed differently from
a TABLE request that is translated within WebFOCUS. We do have a solution
on our web site that reviews this in more detail:

https://techsupport.informatio...om/sps/11043534.html

TECHNIQUE

How to SQL statement trace with synonym for stored procedure

Problem:
How to see the SQL statement trace with synonym for stored procedure.

Running a request against a synonym for a DB2 Stored procedure. They want to
see the SQL statement (SQLSTMT) that are generated and passed to DB2.

Solution:
Synonyms for stored procedures are processed differently than a synonym for a
table. There are no SQL statements created like they are with a synonym for a
table.

To debug an issue with a request against a synonym for a stored procedure, a
full agent trace is required.

Please let me know if you have any further questions or concerns related
to this issue.

Regards,
Customer Support Services
Information Builders