Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Answered] SQL Trace for Oracle Stored Procedures?
 Login/Join
 
David Briars
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,




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
 
Posts: 822 | Registered: April 23, 2003Report This Post
Rifaz
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: 406 | Location: India | Registered: June 13, 2013Report This Post
David Briars
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: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders