Hi,
I am trying to call Oracle stored proc from a fex file. The signature of SP is as :

 
 CREATE OR REPLACE PROCEDURE          SOLV_UPD_RPT_DT   (
 pi_account_no                   VARCHAR2 DEFAULT 'ALL',
 pi_run_date                     DATE DEFAULT SYSDATE,
 po_error_code             out   NUMBER     )
 

Here, we have two input variable and one output variable. I am calling this SP
in my fex as below:

 
 -* File callsp.fex
 -SET &ECHO = ON;
 -SET &ECHO = ALL;
 
 -DEFAULT &ACCT = 'ABC123';
 -DEFAULT &REQDATE = '03/04/2012';
 -DEFAULT &V_OUT= NULL;
 
 ENGINE SQLORA SET VARCHAR OFF
 ENGINE SQLORA SET DEFAULT_CONNECTION dev1
 ENGINE SQLORA
 
 EX FRD_MAIN.SOLV_UPD_RPT_DT '&ACCT','&REQDATE','&V_OUT';
 
 TABLE FILE SQLOUT
 PRINT
 *
 ON TABLE HOLD AS SOLV
 
 END
 -RUN
 

When I run the above code, it throws below error:

 (FOC1400) SQLCODE IS 6550 (HEX: 00001996)
 : ORA-06550: line 1, column 54:
 : PLS-00363: expression 'NULL' cannot be used as an assignment target
 : ORA-06550: line 1, column 7:
 : PL/SQL: Statement ignored L (FOC1405) SQL PREPARE ERROR.
  

I think there is problem with the output variable here. It is being treated as
input varible.
If I remove the output variable and call the SP as :
EX FRD_MAIN.SOLV_UPD_RPT_DT '&ACCT','&REQDATE';
It throws error ' PLS-00306: wrong number or types of arguments in call to'
If I use'?' at OUT variable as:
EX FRD_MAIN.SOLV_UPD_RPT_DT '&ACCT','&REQDATE', ? ;
It gives me below error:

 
FOC1400) SQLCODE IS 6550 (HEX: 00001996)
 : ORA-06550: line 1, column 54:
 : PLS-00103: Encountered the symbol ")" when expecting one of the
 : following:
 :    ( - + case mod new not null <an identifier>
 :    <a double-quoted delimited-identifier> <a bind variable>
 :    continue avg count current exists max min prior sql stddev
 :    sum variance execute forall merge time timestamp interval
 :    date <a string literal with character set specification>
 :    <a number> <a single-quoted SQL string> pipe
 :    <an alternatively-quoted string literal with character set
 L    (FOC1405) SQL PREPARE ERROR.
 

Can you please suggest the way to handle this output variable of stored proc in
fex.


Thanks in advance.

This message has been edited. Last edited by: <Kathryn Henning>,

Not sure if you can capture output variable using SQL pass thru stored procedure.
I believe that SQLOUT expects to receive a result set not a variable.
Try creating a master file for the stored procedure. Then call it using a regular table request.

-DEFAULT &ACCT = 'ABC123';
-DEFAULT &REQDATE = '03/04/2012';
  
TABLE FILE SOLV_UPD_RPT_DT
PRINT
   PI_ACCOUNT_NO
   PI_RUN_DATE
   PO_ERROR_CODE
   
WHERE PI_ACCOUNT_NO EQ '&ACCT';
WHERE PI_RUN_DATE EQ DT(&REQDATE);
END
-RUN