Focal Point
[CLOSED] Calling Stored procdure from webfocus

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1527078395

November 18, 2010, 05:06 AM
Tyson
[CLOSED] Calling Stored procdure from webfocus
I'm calling a stored procedure from WebFocus but I’m not getting any output when I execute the query from MRE. Below is the code snippet and stored procedure.

ENGINE SQLORA SET DEFAULT_CONNECTION MSSRPTD
SQL SQLORA SET CONVERSION LONGCHAR ALPHA
SQL SQLORA PREPARED SQLOUT FOR
-mrnoedit EX CTS.XYZ_3()
-* -mrnoedit EX CTS.XYZ_3
-* -mrnoedit ex 'CTS.XYZ_3'
-* -mrnoedit ex 'CTS.XYZ_3()'
eND
-RUN
TABLE FILE SQLOUT
PRINT *
END
message :
---------
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
when i run the stored produre, i do get results but not when i run from webfocus.

Below is the stored procudure i'm calling thru webfocus
Procedure code

create or replace
procedure xyz_3(V_DOC_NUM OUT NUMBER, V_ORD_TYPE OUT VARCHAR2) as
v_document_number access_service_request.document_number%type;
v_order_type access_service_request.order_type%type;
begin
--dbms_output.put_line('mahesh');
SELECT document_number, order_type
into v_document_number,v_order_type
FROM
ACCESS_SERVICE_REQUEST
Where rownum=1;
dbms_output.put_line(v_document_number);
v_doc_num:= v_document_number;
dbms_output.put_line('V_DOC_NUM' || v_doc_num);
dbms_output.put_line(v_order_type);
v_ord_type:= v_order_type;
dbms_output.put_line('V_ORD_TYPE' || v_ord_type);
end;

Your response would be highly appriciated.

Thanks,
Tyson.

This message has been edited. Last edited by: Kerry,
November 18, 2010, 11:08 AM
Kamesh
If you run the stored procedure on TOAD, is that working?


WFConsultant

WF 8105M on Win7/Tomcat
November 18, 2010, 02:25 PM
BlueZone
Try this flavor, without using the SQLOUT notation --

  
ENGINE SQLORA SET DEFAULT_CONNECTION MSSRPTD
SQL SQLORA SET CONVERSION LONGCHAR ALPHA
SQL SQLORA PREPARED SQLOUT FOR
-mrnoedit EX CTS.XYZ_3()
TABLE
ON TABLE HOLD AS HLD1
END
-RUN
TABLE FILE HLD1
PRINT *
END
-EXIT


I have had similar supression of output sometimes when using SQLOUT and this always works. I did not have a chance to review in detail the SQLOUT functionality and its features.


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
November 19, 2010, 12:20 AM
Tyson
Thanks for the reply... i will try this option


WebFOCUS 7.7
Windows, all output
November 22, 2010, 03:41 AM
Tyson
still not able to retrieve data from procedure...below is the package i tried.
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 network_location
where location_id = locatn;
end test_proc;
end;


if i run this procedure in oracle, it is giving me output but not thru webfocus. Below is the error message i'm getting

"ERROR:ERROR_MR_FEX_NOT_FOUND Cant create item object based on provided item Key CTS.TEST_PACK.TEST_PROC."


WebFOCUS 7.7
Windows, all output
November 22, 2010, 04:35 AM
Computix
AFAIK, the syntax is different:

SQL SQLORA PREPARED SQLOUT FOR
-mrnoedit EX CTS.XYZ_3()
;
END
-RUN
TABLE FILE SQLOUT
PRINT *
END

It is also possible, that you need EXEC instead of EX.

regards,
Markus


WF 7.6.6 (MRE,BID, DevStudio, partly RC) on Windows 2003 /Apache/Tomcat
Output: HTML,Excel,PDF,PPT
Adapters: SQL Server, DB2, Oracle
December 01, 2010, 01:25 AM
RICKY BOND
I have followed the given code. but I do not get any result. I just get the below messge. Suprisigly, there is no error message displayed but no output either Frowner.


ENGINE SQLORA SET DEFAULT_CONNECTION MSSRPTD
SQL SQLORA SET CONVERSION LONGCHAR ALPHA
SQL SQLORA PREPARE SQLOUT FOR
EX CTS.XYZ_3()
;
END
TABLE FILE SQLOUT
PRINT *
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


Webfocus mre, Windows
Excel,Html,PDF
January 13, 2011, 07:23 AM
SriAravind
Try this one.....

ENGINE SQLORA SET DEFAULT_CONNECTION MSSRPTD
SQL SQLORA SET CONVERSION LONGCHAR ALPHA
-STARTPROC
SQL SQLORA
EX CTS.XYZ_3();
END
TABLE ON TABLE HOLD AS SQLOUT
END
-RPT
TABLE FILE SQLOUT
PRINT *
END
-THEEND


WebFocus Version 7.7.05
Windows, HTML/PDF/EXL2K/AHTML