pass document list as an array to an sql stored procedure

, ,
1

Hi Community,
i need to pass a document list as an array to an sql stored procedure withoud using temporary table,
i find some java services on the forum but they are not detailed enogh to kow how to use them(what are there input output, all the content …), im not familiar with java services so if it’s the only solution please show me how to implement it step by step,

please find attached a screen shot of my services and also my document List structure

Appreciate your help,
Regards,
Nezha

toArray.png
toArray.png839×498 40.4 KB

myDocumentList.png

2

hi,
please find attached what i want to do after some ammelioration of my document list,
i got this message error :
om.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service ocp.comm.app.jdbc.om.inbound:test.
[ADA.1.316] Cannot execute the SQL statement "? = call testing (?) “. "
(17004) Invalid column type”
Invalid column type

Regards,
Nezha

After.png
After.png604×505 28.1 KB

3

Hi Nezha,

can you share the definiton of the table used for the stored procecure?

An outline of the Stored Procedures signature will be helpfull too.

Additionally provide JDBC Adapter version with Fixes applied.

Regards,
Holger

4

hi Holger,
thank you so much for your help,
actually i don’t have the table used for the stored procedure because i don’t get the real procedure yet from our sql developer, i just use a stored procedure Test to see the feasibility of using an array as input of the stored procedure because it’s what we decide to use, but i think that the struct table will be as my document list.
this is what i use to create this SP :
set serveroutput on
CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
BEGIN
FOR i IN 1…t_in.count LOOP
dbms_output.put_line(t_in(i));
END LOOP;
END;

for JDBC Adapter :
JDBC_9.0_Fix8 version = 9.0 webMethods Adapter 9.0 For JDBC Fix 2

Regards,
Nezha

5

Hi,
pleeaaase any ideas?

Regards,
Nezha

6

hi everyone,
this is the definition of the SP that we should use :
TYPE bl_rec_type IS RECORD (ac_num XXOCP_OM_COM001_AVISCHRGEMNT.AC_NUM%TYPE
,id_bl XXOCP_OM_COM001_LIGNES_BOL.AC_BL_ID%TYPE
,shipped_qty XXOCP_OM_COM001_LIGNES_BOL.SHIPPED_QUANTITY%TYPE
,net_shipped_qty XXOCP_OM_COM001_LIGNES_BOL.NET_SHIPPED_QUANTITY%TYPE
,bl_date XXOCP_OM_COM001_LIGNES_ADC.ACTUAL_SHIPMENT_DATE%TYPE
);
TYPE tab_bl_rec_type IS TABLE OF bl_rec_type INDEX BY BINARY_INTEGER;

PROCEDURE CONFIRM_EXPEDITION (p_bl_recs IN XXOCP_OM_INTEGRATION.tab_bl_rec_type
,x_status OUT VARCHAR2 --OK / KO
,x_msg_ret OUT VARCHAR2 --msg d’erreur en cas de probleme
);
Regards,
Nezha