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,

If you run the stored procedure on TOAD, is that working?

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.

Thanks for the reply... i will try this option

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."

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

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 .


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

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