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     [CLOSED] unable to call oracle stored procedures in webfocus mre
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] unable to call oracle stored procedures in webfocus mre
 Login/Join
 
Member
posted
This is really urgent
I am unable to call a stored oracle procedure from webfocus mre.

can anyone help me in this matter


I am pasting the procedure and the focex file here please help me
THIS IS THE FOCEX

-SET &ECHO = ON;

ENGINE SQLORA SET DEFAULT_CONNECTION MSSRPT
SQL SQLORA SET CONVERSION LONGCHAR ALPHA

-SET &ERRMSG1 = '';
-SET &ERRMSG2 = '';

-CHKMAIN
-IF EDIT(&LOCA,'9') LE ' ' GOTO DIVERR;
-GOTO STARTPROC;


-DIVERR
-SET &ERRMSG1 = 'Please enter LOCATION ID:';
-SET &ERRMSG2 = '';
-GOTO ERRORMSG


-STARTPROC

SQL SQLORA


EXEC CTS.TEST_PACK.TEST_PROC &LOCA ;



TABLE ON TABLE HOLD AS SQLOUT
END



This is package spec


create or replace package test_pack is

type test_variable is record (
CLLI_CODE VARCHAR2(50),
LOCTN VARCHAR2(50));


type test_ref_cur is ref cursor return test_variable;

procedure test_proc
(ref_other in out test_ref_cur,
locatn in number
);

end;

This is package body



create or replace package body test_pack is


procedure test_proc
(ref_other in out test_ref_cur,
locatn in number
)
is
begin

open ref_other for
select clli_code,location_name from location
where location_id = locatn;

end test_proc;

end;

This message has been edited. Last edited by: Kerry,


Webfocus mre, Windows
Excel,Html,PDF
 
Posts: 4 | Registered: November 22, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You don't say what the result is. Do you get an error message? If so what is the message. If not, then what happens when you execute this fex? Did you switch on tracing and look at the result? Did you run outside of MRE and if so what happens then? Where does the &LOCA parameter get its value?


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
this is the error message


(FOC1400) SQLCODE IS 900 (HEX: 00000384)
: ORA-00900: invalid SQL statement
(FOC1414) EXECUTE IMMEDIATE ERROR.
0 ERROR AT OR NEAR LINE 41 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CLLI_CODE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
END


&loca value is given dynamically by me

It is not able to read the procedure
so could u please tell me how to use oracle stored procedures in webfocus mre


Webfocus mre, Windows
Excel,Html,PDF
 
Posts: 4 | Registered: November 22, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Ricky, your error message seems to indicate that it can read the stored procedure, and that there is an error within the stored proc itself.

I always thought that when calling a stored proc from MRE you had to put -MRNOEDIT BEGIN and END tags around the stored proc call? In our environment, if you don't do that, WebFOCUS cannot locate the stored procedure because it doesn't have the proper connection information. I don't see that in your code, but again, the error you show seems to indicate that WF is finding the stored proc but the error is in the proc itself.


______________________
WF Version: Prod/Test : WebFOCUS 7.67; ETL 7.67

OP system: WIN2K
 
Posts: 18 | Registered: June 28, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Is a "package" a "stored procedure"? Did you check for the ORA-00900 error?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
You should read the Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS documentation:

Using SQL Passthru is supported for Oracle stored procedures. These procedures need to
be developed within Oracle using the CREATE PROCEDURE command.

Note: Calling a stored procedure using SQL Passthru only allows the processing of one
answer set per invocation. If multiple answer sets are expected, the CREATE SYNONYM
mechanism is preferred.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
But here i am calling a package(where the procedure resides) so i cannot write CREATE PROCEDURE command.
I went through the document and over there also he used the same concept
so could u please tell me a way so that i could get around this problem


Webfocus mre, Windows
Excel,Html,PDF
 
Posts: 4 | Registered: November 22, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Ricky,

This is the code that I use to call an Oracle stored procedure from within MRE. The procedure returns the data in a ref cursor.

-MRNOEDIT BEGIN
SQL SQLORA SET SERVER ods
SQL SQLORA EX rpt_appl.XXX_report_queries.pims_wkly_ops_drilldown
'&GNAME',
'&AREA_NAME';
-MRNOEDIT END
-*
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS XXXXX
END
 
Posts: 4 | Registered: April 18, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] unable to call oracle stored procedures in webfocus mre

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