Oracle Cursor as input

, ,
1

Hi All…

I have a requirement for sending order line details to an Oracle Stored procedure.
Does anybody have worked with OracleCursor type as input parameter into stored procedure service?

I am using webMethods 6.0.1; JDBC adapter 6.0.3; Oracle 9i DB.

Karthik

2

Hi Karthik,

In Stored Procedure(without signature),do the following:

  1. click on “Parameter tab”,
    insert row,
    always in first row :
    “Param JDBC Type” should be “ORACLECURSOR”,
    “ParamName” should be “”
    “ParamType” should be “INOUT”

second row, fill your regular inputs…with Param JDBC Type,ParamName,ParamType etc…

  1. Click on Resultset Tab
    Insert row for “result SetName” it will have value “check from your stored procedure the INOUT variable”
    in “columnName” write the fields which this resultset contains.
    you can get these fields from storeProcedure declaration (all variables with “OUT” or “INOUT” types).

Let me know if you have concerns

Regards,
Puneet Saxena

3

Hi Puneet,

As i’m also facing similar pbm maybe you will be able to help me out,

i’m calling a sp from wm using stored proc with signature. There are two IN/OUT parameters in this SP each of datatype Oracle Curser Ref and PL/SQL RECORD. When i try to run this sp i’m getting the error,

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service ss.b2b.PO.adapterService:ssi_out_pkg.
[ADA.1.316] Cannot execute the SQL statement “{call APPS.L3WM_OUT.RUN_ME( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. "
[SIZE=2][COLOR=#0000ff]FONT=Arial Invalid column type"[/color][/size][/FONT]
Invalid column type

The expr field is left blank for these two IN/OUT params and hence they come under output params. Can you please tell me how to handle these datatypes.

Thank you very much for ur time. Any help is appreciated.

Regards,
Ramya

4

Hi Puneet,

As i’m also facing similar pbm maybe you will be able to help me out,

i’m calling a sp from wm using stored proc with signature. There are two IN/OUT parameters in this SP each of datatype Oracle Curser Ref and PL/SQL RECORD. When i try to run this sp i’m getting the error,

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service ss.b2b.PO.adapterService:ssi_out_pkg.
[ADA.1.316] Cannot execute the SQL statement “{call APPS.L3WM_OUT.RUN_ME( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. "
[SIZE=2][COLOR=#0000ff]FONT=Arial Invalid column type"[/color][/size][/FONT]
Invalid column type

The expr field is left blank for these two IN/OUT params and hence they come under output params. Can you please tell me how to handle these datatypes.

Thank you very much for ur time. I can use all the help u ppl can provide. Really appreciate it.

Regards,
Ramya