Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Answered] SQL Trace for Oracle Stored Procedures?
Go
New
Search
Notify
Tools
Reply
  
[Answered] SQL Trace for Oracle Stored Procedures?
 Login/Join
 
Master
posted
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,




Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2012/Tomcat Standalone Workstation: Windows 7/IE 11 Database: Oracle 12c/Netezza Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer, & ReportCaster

 
Posts: 765 | Registered: April 23, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
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


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 405 | Location: India | Registered: June 13, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 765 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Answered] SQL Trace for Oracle Stored Procedures?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.