cancel
Showing results for 
Search instead for 
Did you mean: 

calling an oracle stored procedure with output parameter

Former Member
0 Kudos

Hi,

I am trying to execute a procedure with 2 input and 1 output parameters by using SQLQuery. I tried each mode (Command, FixedQuery & FixedQueryWithOutput) with following statements.

call cm_test_prod([Param.1],[Param.2],[Param.3])

Error:"missing expression "

call cm_test_prod([Param.1],[Param.2],?)

Error:"wrong number or types of arguments in call to 'CM_TEST_PROD'"

call cm_test_prod([Param.1],[Param.2],:var)

Error:"wrong number or types of arguments in call to 'CM_TEST_PROD' "

or

Error: "not all variables bound"

If I use without output parameter, in Command mode, It works fine.

call cm_test_prod([Param.1],[Param.2])

or

call cm_test_prod('[Param.1]','[Param.2]')

works without error.

SAP XMII: Version 12.0.5 Build(126)

Oracle 10G

Any help would be greatly appreciated.

Thanks,

Tunur.

Edited by: Namik Tunur on Dec 14, 2010 3:34 PM

Edited by: Namik Tunur on Dec 14, 2010 3:36 PM

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

You will need to use FixedQueryWithOutput for Oracle procedure calls. What are the datatypes for your inputs and outputs?

Regards,

Mike

Former Member
0 Kudos

Hi Namik,

try this:

Create Package and Procedure in DB ORACLE:

Create or replace package MY_PKG is

Type ZNCursor is REF CURSOR;

procedure MyProcedure(P1 in INTEGER, P2 in Varchar(100), Ret OUT ZNCursor);

end MY_PKG;

-


XXXXXXXXXXXXXXXXXX----


Create or replace package body MY_PKG is

procedure MyProcedure(P1 in INTEGER, P2 in Varchar(100), Ret OUT ZNCursor) is

BEGIN

OPEN Ret FOR

Select P1 as field1, P2 as field2

from dual;

END MyProcedure;

end MY_PKG;

-


XXXXXXXXXXXXXXXXXX----


In MII, Query Template type FixedQueryWithOutput

Query = Call MYPKG.MyProcedure(1,'Test FixedQueryWithOutput',?)

0 Kudos

Hi Namik,

The single quotes are where I was going with my question of datatypes. If the datatype of the input or output is a text type (string or whatever the Oracle equivalent is), then it needs the single quotes (or tics). This may take some testing and 12.0 is relatively forgiving if you put tics around numeric datatypes (more so than 12.1 in my experience), but leaving them off of alphabetic datatypes is not handled as well.

So just to make sure it is clear when you build your SQL Scripts they should be like:

For alphabetic datatypes (text, string, memo, etc.):

'[Param.1]'

For numeric datatypes (integer, double, float, etc.):

[Param.1]

Regards,

Mike

Former Member
0 Kudos

Hi,

@Marcelo,

I used your package just by replacing 'varchar2' instead of 'varchar(100)''. I got the error message 'ORA-00900: invalid SQL statement'. I didn't understand how we handle output parameter with this:

Query = Call MYPKG.MyProcedure(1,'Test FixedQueryWithOutput',?)

What is Query in here? If we have two output parameters, how would we get the outputs?

My procedure is:

CREATE OR REPLACE

PROCEDURE XXXXX.CM_TEST_PROD_OUTPUT(v1 in number,v2 in number, v3 out number) IS

BEGIN

update test_table set no2 = (v1+v2);

commit;

v3 := v1+v2;

END;

..

declare

a number;

BEGIN

cm_test_prod_output(1,6,a);

END;

if I use this

call cm_test_prod_output([Param.1],[Param.2],?)

with FixedQueryWithOutput mode,

I get the following error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'CM_TEST_PROD_OUTPUT'

@Mike,

I already tried both with quotes and without quotes, also I tried both with number output and with string output.

As I mentioned before, if I use number inputs without output in Command mode,

call cm_test_prod(http://Param.1,http://Param.2)

or

call cm_test_prod('http://Param.1','http://Param.2')

works succesfully.

Regards,

Tunur

Former Member
0 Kudos

Hi,

On MII Query Template type "FixedQueryWithOutput" the return is a recordset.

This correspond a "?" in SQL command "CALL PKG.SP(P1,P2,?)"

For your procedure you can make this:

PROCEDURE XXXXX.CM_TEST_PROD_OUTPUT(v1 in number,v2 in number, v3 out ZNCursor) IS

BEGIN

update test_table set no2 = (v1 v2);

commit;

Open out for

Select v1 v2 as ret from dual;

END;

The type ZNCursor need be declared, see in my package:

Type ZNCursor is REF CURSOR;

If you have more then one output params you can make:

select p1,p2,p3 from dual;

Former Member
0 Kudos

Thank you Marcelo,

CREATE OR REPLACE

package XXXX.MY_PKG is

Type ZNCursor is REF CURSOR;

procedure MyProcedure(P1 in INTEGER, P2 in INTEGER, Ret OUT ZNCursor);

end MY_PKG;

CREATE OR REPLACE

package body XXXX.MY_PKG is

procedure MyProcedure(P1 in INTEGER, P2 in INTEGER, Ret OUT ZNCursor) is

BEGIN

update test_table set no2 = (p1 + p2);

commit;

OPEN Ret FOR

Select P1+P2 from dual;

END MyProcedure;

end MY_PKG;

Writing

Call MY_PKG.MyProcedure(1,1,?)

into "Fixed Query" section solved the problem.

Regards,

Tunur.

Answers (0)