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     [SOLVED] How do I call a Oracle stored procedure using ref cursors in WebFocus?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How do I call a Oracle stored procedure using ref cursors in WebFocus?
 Login/Join
 
Member
posted
I am using Oracle 10g and WebFocus AppStudio.


So I have an Oracle stored procedure. I want to create a report using WebFocus. How do I call the stored procedure.

This is what my Web Focus code looks like now:


SQL SQLORA SET SERVER CPMDEV

SQL SQLORA EX CPMDEV.WF_LONG_DISTANCE_REPORT;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT

END

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 12 | Registered: June 01, 2017Reply With QuoteReport This Post
Master
posted Hide Post
Our standard is to create .mas/.acx files for the stored procedure using the WF GUI Synonym Builder.

The .mas created does a nice job of defining/describing the required filters (INPUT) and columns you can report from (ANSWERSET).

Once the meta data is created use your normal TABLE FILE command to create your report.
 
Posts: 787 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Add END after the SP call, and, don't use the same name for your HOLD file:

SQL SQLORA SET SERVER CPMDEV

SQL SQLORA EX CPMDEV.WF_LONG_DISTANCE_REPORT;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUTX
END

hth


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1961 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Make sure their schema is CPMDEV, if the stored procs are in another schema that you can access, even if in Oracle you "can" call them from CPMDEV, if they reside say in CPMPRC, you need to connect to CPMDEV, but call CPMPRC.WF_LONG_BORING_REPORT

Signed as 'spent a day trying to figure this out'


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
I warmly second David's method of creating masters. Even if you don't use them for a reason or the other, it is a *very* handy reference for the input/answerset. Especially if the proc's aren't nicely available with what connections you have. Or forbid they differ slightly between environments...


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     [SOLVED] How do I call a Oracle stored procedure using ref cursors in WebFocus?

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