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,

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?

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

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.

Is a "package" a "stored procedure"? Did you check for the ORA-00900 error?

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.

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

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