hi all,
I am trying to call a oracle stored proc from a maintain function. The stored proc has two input variables and one output variable. Can someone please help me with the syntax to achieve this or any work around for this.

Thanks in advance.

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

The easiest way to do this is with the EXEC command. The syntax is:

EXEC procedure FROM var1 var2 INTO stack

Here, procedure is the name of the Oracle stored procedure.
Var1 is the first variable and Var2 is the second variable.

If you echo the output variable to the screen with a TYPE statement you can retrieve it in Maintain with FOCMSG.MSG. If you do this then you don't need the INTO stack parameter on the EXEC command. You will be able to:
COMPUTE VAR = FOCMSG.MSG
to retrieve the output from the EXECd procedure.

Mark

This is how I call an oracle store procedure.

  
-DEFAULTS &TNK_STR='599';
-*VARCHAR
-DEFAULTS &SITE_STR=10;
-*NUMBER
-DEFAULTS &FR_DATE_STR='07/01/2011';
-*VARCHAR
-DEFAULTS &TO_DATE_STR='09/30/2011';
-*VARCHAR
 
ENGINE SQLORA SET VARCHAR OFF
ENGINE SQLORA SET DEFAULT_CONNECTION dev07
ENGINE SQLORA
 
 
EX WMGAPPL.INV_SORT.SORT_INV_FOR_WMG0183 '&TNK_STR',&SITE_STR,'&FR_DATE_STR','&TO_DATE_STR';
 
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS SEQ_NUM
END

Hi Mark,
The signature of my Stored Proc is:

 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     )

In my maintain function I am using below code:

 
Case AddClient

COMPUTE CurrDate/HYYMDm = HGETC(26, CurrDate);
compute proc/a25v = 'FRD_MAIN.SOLV_UPD_RPT_DT';

Compute rc/i8;
Compute SQLINSFND/A1000 = "INSERT INTO FRD_REPORTING.solv_client_setup_view solv VALUES ('"|acctno|"','"|cdbno|"' ,'"|category2|"','"|clientname|"' ,sysdate,'"|isin|"' ,'"|sedol|"' ,'"|invest|"','"|icrs|"' ,'"|gfascode|"','"|currency|"','"|status|"' ,'"|distdate|"','"|distmethod|"','"|language|"' ,'"|endyear|"','"|D1Flag|"','"|D1SFlag|"' ,'"|D2OFlag|"','"|D2TFlag|"','"|D3Flag|"' ,'"|D4Flag|"','"|D5Flag|"' ,'"|D6Flag|"','"|email|"','"|emailcc|"','"|holdparty|"',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,'"|userIds|"',sysdate )";

rc=sys_mgr.engine("SQLORA","SET DEFAULT_CONNECTION FILFRDD1.world");

rc=sys_mgr.engine("SQLORA",SQLINSFND);
 
rc=sys_mgr.engine("SQLORA","commit");

type "Query <<SQLINSFND";

EXEC proc FROM acctno CurrDate; 
COMPUTE VAR = FOCMSG.MSG;

type "Output<<VAR";

EndCase

 

In my maintain code,I am basically trying to insert a record (based on values provided by user on screen) and then fire a Stored proc after that which will update the inserted record based on some predefined rules.But,When I run this code it throws 'EDA no data' error. If I run it after removing EXEC command, it runs absolutely fine. i.e, Data record gets inserted properly. At DB end, the stored proc is running fine. I am not able to understand the cause of this behaviour.
One more thing,when I directly use stored proc name as ''EXEC FRD_MAIN.SOLV_UPD_RPT_DT FROM acctno CurrDate; it thrown some syntax error. That’s why I kept in a variable 'proc' and then used it in EXEC command. I didn't get the reason for this error.

Can you please help me with the resolution of these problems.

Thanks for your help.

Ok... The in stream INSERT works but performing the EXEC afterwards yields an error. I am pretty sure the issue here is that the name of the EXEC procedure cannot be a variable. You have EXEC proc FROM acctno CurrDate; I am pretty sure that Maintain is going to look for a procedure named proc and not the value of the variable. Maybe you have a procedure named proc somewhere in your APP PATH that Maintain is finding. Try replacing PROC with just SOLV_UPD_RPT_DT and see if Maintain can find that. Make sure the project is in the APP PATH.

If that is not it, continue with this...

Issue a COMMIT before the EXEC command. I see that you are issuing a COMMIT directly to the SQL engine, but Maintain may still think the record is not saved.

Does control return to the Maintain procedure after the EXEC command or do you just get an EDA NODATA screen? If control returns, place a -SET &ECHO=ALL at the beginning of the EXECed procedure. Then display FOCMSG.MSG on your form in an HTMLTABLE. It will display any messages that occur during the execution of the procedure. I also do not see where you are using &1 or &2 in your EXECed procedure. You are passing them in but not retrieving them?

Mark

Hi Mark,
Apologies for late reply. I was on leave for a week returned today only.

I tried your suggestions but unfortunately none of them helped. Finally I comment out the Insert statement and only calling Stored proc in the function. But still control is not returning back to maintain and 'EDA no Data' screen comes up. I modified the code little bit as shown below. I included one output variable 'v_out' while calling the proc as per the signature of the proc.

 

Case AddClient 
COMPUTE CurrDate/HYYMDm = HGETC(26, CurrDate); 
compute v_out/i10;
compute acctno/a50v = 'BMWA100004';
-*compute proc/a25v = 'FRD_MAIN.SOLV_UPD_RPT_DT'; 
$*
Compute rc/i8; 
Compute SQLINSFND/A1000 = "INSERT INTO FRD_REPORTING.solv_client_setup_view solv VALUES ('"|acctno|"','"|cdbno|"' ,'"|category2|"','"|clientname|"' ,sysdate,'"|isin|"' ,'"|sedol|"' ,'"|invest|"','"|icrs|"' ,'"|gfascode|"','"|currency|"','"|status|"' ,'"|distdate|"','"|distmethod|"','"|language|"' ,'"|endyear|"','"|D1Flag|"','"|D1SFlag|"' ,'"|D2OFlag|"','"|D2TFlag|"','"|D3Flag|"' ,'"|D4Flag|"','"|D5Flag|"' ,'"|D6Flag|"','"|email|"','"|emailcc|"','"|holdparty|"',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,'"|userIds|"',sysdate )"; 
rc=sys_mgr.engine("SQLORA","SET DEFAULT_CONNECTION FILFRDD1.world");
 rc=sys_mgr.engine("SQLORA",SQLINSFND); 
rc=sys_mgr.engine("SQLORA","commit");
 type "Query <<SQLINSFND"; 
*$
EXEC SOLV_UPD_RPT_DT FROM acctno CurrDate v_out; 
COMPUTE VAR = FOCMSG.MSG; 
type "Output<<VAR"; 
type "AcctNo<<acctno";
type "z1<<z1";
type "date<<CurrDate";
EndCase
 

Can you please suggest a way to resolve this issue.

Thanks for your help.

Could you please post the stored procedure? It may help to see it. Are you sure that there are no other copies of SOLV_UPD_RPT_DT anywhere else in your APP PATH? Please check that. Also make sure there is a -RUN at the end of the stored procedure.

Is this case being called from a Winform? If so we can use FOCMSG to help debug. Place and HTMLTable on the form, populated by FOCMSG.MSG. In your stored procedure add -SET &ECHO=ALL; to the top of the procedure and make sure there is a -RUN at the bottom. Now anything echoed to the form SHOULD come back to the screen.

Mark

Hi Mark,
Thank for your reply. After using your suggested techniques, I found the cause of error. The problem is with the output variable passed as &3 . My EXECed is as :

 
-SET &ECHO = ON;
-SET &ECHO = ALL;

ENGINE SQLORA SET VARCHAR OFF
ENGINE SQLORA SET DEFAULT_CONNECTION FILFRDD1.world
ENGINE SQLORA

EX FRD_MAIN.SOLV_UPD_RPT_DT '&1','&2','&3';

TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS MYOUT

END
-RUN
 

When calling it from maintain (as mentioned in above post) thie yield below error message:

 
 
	Msg	 
     
 
1	-*TYPE &1;	   
2	-*TYPE &2;	   
3	-*TYPE &3;	   
4	ENGINE SQLORA SET VARCHAR OFF	   
5	ENGINE SQLORA SET DEFAULT_CONNECTION FILFRDD1.world	   
6	ENGINE SQLORA	   
7	EX FRD_MAIN.SOLV_UPD_RPT_DT 'BMWA100004','2013/04/03 06:23:12.699000','0';	   
8	TABLE FILE SQLOUT	   
9	PRINT	   
10	*	   
11	ON TABLE HOLD AS MYOUT	   
12	END	   
13	-RUN	   
14	SQLCODE IS 6550 (HEX: 00001996)	   
15	: ORA-06550: line 1, column 74:	   
16	: PLS-00363: expression '0' cannot be used as an assignment target	   
17	: ORA-06550: line 1, column 7:	   
18	: PL/SQL: Statement ignored	   
19	SQL PREPARE ERROR.	 


 

While calling SP we have to mention output parameter in its signature. Removing &3 yields another error as 'PLS-00306: wrong number or types of arguments in call to'. I am not understading how to include OUT parameter in EX statement so that it runs fine. Do I need to do something in Stored proc to make it work or it can be handled in maintain or fex file itself. Please suggest.

Thanks a lot for your help.

I am afraid I don't understand your question. If the problem is passing a 0 as parm 3, why are you doing it? What does v_out represent? Is this something being returned from the SQL procedure? Something being passed back from the EXEC command? What should it be?

Mark

hi Mark
v_out is OUT param of the called SQL proc. This will return the numeric value 0 on success else something else on failure.
Now while calling stored proc, there would be two input params &1 and &2 for acctno and currdate respectively which is coming from maintain. param &3 will be the OUT param (v_out) returned from stored proc. I want to capture this returned value after its execution.
The problem is that when I call stored proc using EX command,by passing only &1 and &2 it gives an error pls-00306 as I mentioned in previous post.
My question is how to execute the stored proc by passing two input params and fetch the returned value.In EX command do I need to mention OUT param as well? If yes then how?

I hope I made it clear to you.

Thanks for your help.

This is outside the realm of the Maintain command. It's helpful to get the stored procedure working before allowing Maintain to call it. I mean, you can give parm 3 a value instead of 0, but I am not sure why if it's an output variable that the initial value matters.

It seems like SOLV_UPD_RPT_DT is taking &3 as an input variable. Is it possible that the procedure is looking for a different value? See if you can get it to run outside the realm of Maintain and work backward from there.

Mark