Getting o/p from Oracle procedure in MII 14

[Former Member]

Dear Experts,

I have written procedure in oracle with 2 input param and 1 output param.

Tried with CALL USP_UPD_NOTIFICATION('[Param.1]','[Param.2]') but giving error "wrong number of parameter"

Can anyone please let me know syntax for calling procedure from SAP MII so that i can get o/p?

Thanks in advance

Regards,

Neha

[Former Member]

Neha,

I am using fixed query with output as below in the fixed query text box:

declare

indOn MyActivityInsert.ACT_CURSOR;

begin

ActualTrans(:indOn,[Param.1],'[Param.2]');

commit;

end;

ActualTrans is the procedure and indOn is the cursor which is returning the value as document.UpdCall.getValue(1,1) in JavaScript function where UpdCall is iCommand object

<APPLET  style="visibility:hidden" NAME="UpdCall" CODEBASE="/XMII/Classes" CODE="iCommand" ARCHIVE="illum8.zip" WIDTH="1" HEIGHT="1" MAYSCRIPT>

<PARAM NAME="QueryTemplate" VALUE="MyProj/CallQuery">

</APPLET>

Thanks,

Raman N

[Former Member]

Thank you Raman.

Can you please let me know what is MyActivityInsert.ACT_CURSOR?

[Former Member]

Neha,

ACT_CURSOR is of type output OUT and MyActivityInsert is the package name used.

Thanks,

Raman N

[former_member186805]

Also Try with EXEC Procdure_Name Validate if you have selected proper data server and Mode in Query Template

[Former Member]

Dear Ayush,

I have selected Fixed query mode and two input parameter. But issue facing how to access output parameter.

Can you please help with this?

[swaroop_anasane]

Hi Neha,

Basic understanding, the commands that work in your sql studio would work same way in MII as MII would basically route your commands to same sql/db. So if EXEC SQP param_1, param_2 works in studio/sql plus for your case, it should work here as well.

If i remember it correctly, for Oracle/SQL++, the command is execute proc_name('param_1', 'param_2');

It should give you result in MII Rowsets/Rowset/Row format that can readily be utilized in your app.

Note: Commands also depend on data server/connector type like it would be different for ODBC and OLEDB connectors. However, ultimately it depends on source system.

Try below syntax for your case:

exec USP_UPD_NOTIFICATION('[Param.1]','[Param.2]');

or

execute USP_UPD_NOTIFICATION('[Param.1]','[Param.2]');

Make sure you pass params in parameters section.

Hope this helps.

Regards,

Swaroop

[Former Member]

Thank you Swaroop for response.

I have tried with both above syntax and Param.1 and Param.2 as input parameter.

I want to access output variable of procedure.If execute procedure with above syntax and two input parameter then it gives following error:

SQL error has occurred on query ORA-00900: invalid SQL statement

Can you please help me for accessing o/p variable of procedure?

[former_member186805]

Hi Neha, Did you check how many params you created in Stored Procedure definition? There may be mismatch in passing Params and receiving params at SP side. Thanks, Ayush