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     [FEATURE]Calling Oracle Stored Proc in WF8
Go
New
Search
Notify
Tools
Reply
  
[FEATURE]Calling Oracle Stored Proc in WF8
 Login/Join
 
Platinum Member
posted
I've got a weird error I don't know whether it is my end or the DB end and got to do with version 8 or not at all.

I've got the Oracle connection set up, masters work, can run SQL query against a table ok. Call either schema.table or just table I'm fine. Calling a stored procedure however, SQL EX schema.package.proc(variable); returns ORA-25123: Too many components specified in the name.
and SQL EX package.proc returns ORA-04043: object package.proc(variable); does not exist

Executing the same schema.package.proc(variable); in SQL Developer again works.

Now the Oracle DBA wants to know what exactly I am sending to the Oracle, but I seem not to have any SQL trace option on the adapter, regular traces just print the error row but not the handshakes?

I'm a bit at loss as to which component should I start to look at first.

Hrmpf... looks like someone had the same issue 10 years ago, no answers though http://nntp-archive.sybase.com...2544C7@onramp.net%3E

This message has been edited. Last edited by: Hank W.,


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 114 | Location: Brighton UK | Registered: February 19, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do you have something similar as for SQL trace ?
SET SQLTOPTTF = ON
SET TRACEOFF  = ALL
SET TRACESTAMP= OFF
SET TRACEWRAP = 72
SET TRACEON   = SQLTRANS
SET TRACEON   = SQLAGGR//CLIENT
SET TRACEON   = STMTRACE//CLIENT
SET TRACEON   = STMTRACE/1/CLIENT
SET TRACEON   = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET MSG       = ON
SET HOLDSTAT  = ON
SET EMGSRV    = ON
-SET &ECHO    = ALL;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2298 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Yes, Martin, though I get the SQL nicely, I'd want to see the handshake & such as well.


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 114 | Location: Brighton UK | Registered: February 19, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
OK, so the mystery got solved when I started to look at the database "through the adapter". The stored procedures were not in schema-a which is my user schema. They're in schema-x, and schema-a has pointers to the packages set up. Which the WF adapter doesn't recognize. Now when I do the call to the package, while I connect to schema-a, I need to define the actual schema in the call.
SQL EX schema-x.package.proc(variable);

Mystery solved, just a bit more McGyvering.


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 114 | Location: Brighton UK | Registered: February 19, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [FEATURE]Calling Oracle Stored Proc in WF8

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