Passing array RecordList to stored procedure

11

Hi
At oracle end the following steps are required
1.Create a Type in Oracle
ex. CREATE OR REPLACE type recordarray as VARRAY(10000) of varchar2(250)
2.Create a Stored procedure that takes as an input the variable of the created type.
ex CREATE OR REPLACE PROCEDURE procName(Array IN RECORDARRAY,sError OUT VARCHAR2) IS
3. In the procedure create a loop over the type.

At webMethods IS end following steps are required

  1. Create a java service that takes as an input a string list.
  2. Create a string array in the java service as
    String anArrayIn = IDataUtil.getStringArray( pipelineCursor, “InputstringListName” );
  3. Register a thin driver
  4. Create a connection object.
  5. Create variable of the type oracle.sql.ArrayDescriptor & oracle.sql.Array
    oracle.sql.ArrayDescriptor anArrayDescriptor = new oracle.sql.ArrayDescriptor(“Db Type name”,con);
    oracle.sql.ARRAY anARRAYin = new oracle.sql.ARRAY(anArrayDescriptor, con, "ArrayCreatedInJavaService);
  6. Call the stored procedure using oracle.jdbc.OracleCallableStatement
  7. Use setArray function to pass the array to the strored procedure
    cstmt.setARRAY(2, anARRAYin)

Do let me know in case you require any other information.

Thanks
Deepali