Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calling Stored procdure from webfocus

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Calling Stored procdure from webfocus
 Login/Join
 
Member
posted
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,
 
Posts: 7 | Registered: July 19, 2010Report This Post
Master
posted Hide Post
If you run the stored procedure on TOAD, is that working?


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Member
posted Hide Post
Thanks for the reply... i will try this option


WebFOCUS 7.7
Windows, all output
 
Posts: 7 | Registered: July 19, 2010Report This Post
Member
posted Hide Post
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
 
Posts: 7 | Registered: July 19, 2010Report This Post
Silver Member
posted Hide Post
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
 
Posts: 38 | Location: Austria, Linz | Registered: June 19, 2009Report This Post
Member
posted Hide Post
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
 
Posts: 4 | Registered: November 22, 2010Report This Post
Gold member
posted Hide Post
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
 
Posts: 63 | Registered: January 12, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calling Stored procdure from webfocus

Copyright © 1996-2020 Information Builders