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

Question

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);

I can see a possible issue, but I'm struggling to get the same error you are seeing. Which line in your code is that exception being thrown by; and which version of Oracle, Java and the JDBC driver are you using? — Alex Poole
– Alex Poole, Commented Oct 23, 2017 at 16:51
If I comment callstSave.registerOutParameter(1, OracleTypes.ARRAY); able to call procedure but not getting out array .. — Ritesh
– Ritesh, Commented Oct 24, 2017 at 11:19

Alex Poole · Accepted Answer · 2017-10-24 11:21:02Z

1

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.

answered Oct 24, 2017 at 11:21

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related