Calling Oracle Stored Procedure and Query from FEX FIle 1

[yajmere]

HI
i have a problem that i can not execute both Procedure and SQL Query from same fex.
For my applicaton i need to run procedure first which appends data to 2 oracle table and then i have to use sql query to retrive data back.
When i run the request it inserts records in Oracle tables. but it does not show any Query Output in browser window.
Because i have used SET ECHO=ALL;
it displays all procedure code in browser alongwith SQL Query. Surprisingly it does not show any error message.

SQL Query which is displayed if i copy and run on SQL PLUS it gives me exact desired results. can anyone please tell me what is wrong Am i doing?


CODE FOR FEX File

SET ASNAMES=ON
-SET &ECHO = ALL;
-SET &CUSER = GETUSER(USERID);
-SET &SID = &CUSER || &YYMD.EVAL || &TOD.EVAL;
-MRNOEDIT BEGIN
SQL SQLORA
EX DBO.TOPNINSERT '&SID', '10', 'M', '200101', '200212', 'T';
SELECT
QUERY_RESULTS.ORDERING_NUMBER ,
QUERY_RESULTS.SESSION_ID ,
SUBSTR(ATTRIBUTE_1,1,100) ATTRIBUTE_1,
SUBSTR(ATTRIBUTE_2,1,100) ATTRIBUTE_2,
SUBSTR(ATTRIBUTE_A,1,100) ATTRIBUTE_A,
SUBSTR(ATTRIBUTE_B,1,100) ATTRIBUTE_B
FROM
QUERY_RESULTS,
QUERY_SUBSIDIARY_RESULTS
WHERE
QUERY_RESULTS.SESSION_ID = QUERY_SUBSIDIARY_RESULTS.SESSION_ID
AND QUERY_RESULTS.ORDERING_NUMBER = QUERY_SUBSIDIARY_RESULTS.ORDERING_NUMBER
AND
QUERY_RESULTS.SESSION_ID= '&SID';
-RUN
TABLE FILE SQLOUT
PRINT ORDERING_NUMBER AS 'ORDER NO'
SESSION_ID AS 'SESSION'
ATTRIBUTE_1 AS 'ATTR 1'
ATTRIBUTE_2 AS 'ATTR 2'
ATTRIBUTE_A AS 'ATTR A'
ATTRIBUTE_B AS 'ATTR B'
ON TABLE SET PRINT ONLINE
-MRNOEDIT END
-RUN




Thanks
Yogesh

 

[FWIT]

I believe that you can correct the problem by putting an "END" after the execution of the stored procedure (after the ";&quot, after the select (again after the ";&quot and at the end of your table file. Hope this helps.

 

[FWIT]

Correction: Leave off the "end" after the "select". Also delete the -RUN before the "table file sqlout" (this has been tested in 4.3.6 sp2 )