Skip to Content
avatar image
Former Member

calling an oracle stored procedure with output parameter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 14, 2010 at 05:23 PM

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

    Regards,

    Mike

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.