CODEWITHSUNDEEP
javaarraysoracle-databasestored-proceduresjdbc
Ritesh
Ritesh

Reputation: 1

java.sql.SQLException: Invalid argument(s) in call in Passing custom array to procedure

I am trying to call a procedure with custom type value but getting error

java.sql.SQLException: Invalid argument(s) in call.

Not sure what is wrong?

ORACLE code :

CREATE OR REPLACE TYPE obj_cms_custodian_exigo_code
AS OBJECT
(
    fund_cust_exigocode_id           NUMBER,
    portfolio                        VARCHAR2(10),
    custodian_code                   VARCHAR2(15),
    security_exigo_code              VARCHAR2(30),
    cash_exigo_code                  VARCHAR2(30),



  CONSTRUCTOR FUNCTION obj_cms_custodian_exigo_code(
    fund_cust_exigocode_id           NUMBER,
    portfolio                        VARCHAR2,
    custodian_code                   VARCHAR2,
    security_exigo_code              VARCHAR2,
    cash_exigo_code                  VARCHAR2
  )
  RETURN SELF AS RESULT
)

CREATE OR REPLACE TYPE ary_cms_custodian_exigo_code AS TABLE OF obj_cms_custodian_exigo_code

PROCEDURE save_cms_cv_cust_exigo_code
  (
      p_fund_cust_exigocode         IN OUT ary_cms_custodian_exigo_code,
      p_user_alias                  IN vew_user_alias.alias%TYPE
  )
  ...

Java Code :

Object[] array_of_records=new Object[1];
Object[] custodianExigoSaveKeyRecord=new Object[5];



STRUCT oracleRecord;

StructDescriptor custodianExigotype;  //for type
ArrayDescriptor custodianExigotypeTable;//for array



//create descripter for eatch oracle record

custodianExigotype = StructDescriptor.createDescriptor("OBJ_CMS_CUSTODIAN_EXIGO_CODE", connNative);


//create a descripter for array of records
custodianExigotypeTable = ArrayDescriptor.createDescriptor("ARY_CMS_CUSTODIAN_EXIGO_CODE", connNative);


custodianExigoSaveKeyRecord[0]=Double.parseDouble("123");
custodianExigoSaveKeyRecord[1]="abc";
custodianExigoSaveKeyRecord[2]="abc";
custodianExigoSaveKeyRecord[3]="test";
custodianExigoSaveKeyRecord[4]="abc";
      System.out.println(" custodian Exigo save 22222222"+i);

  oracleRecord=new STRUCT(custodianExigotype,connNative,custodianExigoSaveKeyRecord);
   System.out.println(" custodian Exigo after creating STRUCT"+i);

   array_of_records[0]=oracleRecord;

  System.out.println(" custodian save 3333333333");

  ARRAY oracleArray=new ARRAY(custodianExigotypeTable,connNative,array_of_records);

  System.out.println("oracle array .."+oracleArray);

  System.out.println(oracleArray.getLength()+"  length of array .. "+oracleArray.getDescriptor());

  callstSave= conn.prepareCall("{call pkg_cms_custodian_exigo_code.save_cms_cv_cust_exigo_code(?,?)}");

  callstSave.setArray(1,oracleArray);
  callstSave.setString(2, "ABCUSER");//custodianExigoVo.getUserId()



  callstSave.registerOutParameter(1, OracleTypes.ARRAY);

  System.out.println("before calling proce dure ......");

  callstSave.execute();

  System.out.println("after calling procedure .........");

  Array outarray =callstSave.getArray(1);

Upvotes: 0

Views: 2736

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

I can't exactly duplicate what you're seeing because I don't have an Oracle 10g DB available, but I can get slightly different errors with variations of your Java and JDBC version. (Why you're using JDBC14 is a separate question; 10g isn't supported of course, but see this and support doc ID 401934.1 for more on compatible versions).

Those errors can be resolved by including the type name in the registerOutParameter() call:

callstSave.registerOutParameter(1, OracleTypes.ARRAY, "ARY_CMS_CUSTODIAN_EXIGO_CODE");

Incidentally, your call to oracleArray.getLength() isn't going what you think and is probably always going to return zero; use oracleArray.length() instead to see the actual array size.

Upvotes: 1

Related Questions