0

This is my code where METHODARGDATATYPE_VARRAY is a varray at 5th column in my table and first column has auto generated sequence concatenated with 't'.

    List<String> str= new ArrayList<String>();
    str.add("argdataty1");
    str.add("argdataty2");

the above part is written in main method in which i will set bean values 

    testCaseIDandDetailsBean.setMethodArgDataType(str);

and pass it to my method where adding to db is performed

    public String addTestCaseIDandDetails(TestCaseIDandDetailsBean testCaseIDandDetailsBean)

code in the method is as bellow

     List<String> str= new ArrayList<String>();
     str=testCaseIDandDetailsBean.getMethodArgDataType();

    String arrayElements[] = new String[30];
      int i1=0;
     for(String itr : str){
        arrayElements[i1]=itr.toString();
        System.out.println(arrayElements[i1]);
        i1++;
     }
    String arrayElements[] = { "Test3", "Test4" };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor
                                    ("METHODARGDATATYPE_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, arrayElements);

    String sql="insert into TestCaseIDDetails values (concat('t',TestCaseID_sequence.nextval),?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement (sql);
    ps.setString(1,testCaseIDandDetailsBean.getClass_name()) ;
    ps.setString(2,testCaseIDandDetailsBean.getMethod_name()) ;
    ps.setString(3,testCaseIDandDetailsBean.getMethodReplacement()) ;
      ((OraclePreparedStatement)ps).setARRAY (4, newArray);

    ps.execute ();

And I am getting errors as:

    java.sql.SQLException: Exceeded maximum VARRAY limit 
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.oracore.OracleTypeCOLLECTION.pickle81(OracleTypeCOLLECTION.java:707)
at oracle.jdbc.oracore.OracleTypeADT.pickle81(OracleTypeADT.java:1980)
at oracle.jdbc.oracore.OracleTypeADT.linearize(OracleTypeADT.java:1364)
at oracle.sql.ArrayDescriptor.toBytes(ArrayDescriptor.java:697)
at oracle.sql.ARRAY.toBytes(ARRAY.java:680)
at oracle.jdbc.driver.OraclePreparedStatement.setArrayCritical(OraclePreparedStatement.java:5958)
at oracle.jdbc.driver.OraclePreparedStatement.setARRAYInternal(OraclePreparedStatement.java:5918)
at oracle.jdbc.driver.OraclePreparedStatement.setARRAY(OraclePreparedStatement.java:5898)
at implementation.TestCaseIDandDetailsDAOImpl.addTestCaseIDandDetails(TestCaseIDandDetailsDAOImpl.java:61)
at implementation.TestCaseIDandDetailsDAOImpl.main(TestCaseIDandDetailsDAOImpl.java:141)

And my db tables are:

   CREATE or replace TYPE METHODARGDATATYPE_VARRAY AS VARRAY(20) OF varchar2(30);
   create table TestCaseIDDetails(
      testcaseID varchar2(20) primary key,
      classname varchar2(20) not null,
      methodname varchar2(20) not null,
      MethodReplacement char(2) check(MethodReplacement in ('y','n')),
      MethodArgDataType METHODARGDATATYPE_VARRAY);

 Create sequence TestCaseID_sequence minvalue 1 start with 1 increment by 1 ;
Nagaumadevi Palaparthy
  • 63
  • 13
  • I had tried CREATE or replace TYPE METHODARGDATATYPE_VARRAY AS VARRAY(20) OF varchar2(30 char); but not working – Nagaumadevi Palaparthy Oct 30 '15 at 08:07
  • possible duplicate:http://stackoverflow.com/questions/4347888/sql-error-ora-12899-value-too-large-for-column – soorapadman Oct 30 '15 at 08:10
  • 1
    This looks like a problem with your `MethodReplacement`, not with your `VARRAY`: `ORA-12899: value too large for column "ER706221"."TESTCASEIDDETAILS"."METHODREPLACEMENT" (actual: 10, maximum: 2)` – Peter Lang Oct 30 '15 at 08:11
  • 1
    can you check at what you provide for column name : MethodReplacement whether 'y' or 'n', i mean you were provided more then single char value that's why it's get value too larger . – Vishal Gajera Oct 30 '15 at 08:22
  • sry it has been taken care of there is another issue – Nagaumadevi Palaparthy Oct 30 '15 at 08:35

1 Answers1

1

The value you put in the 4th column is too large. By the table definition, it accepts maximum 2 characters, and the value must be either 'y' or 'n'. (btw, it's a weird definition because 'y' and 'n' are single chars, no need for 2). The problem is that you give it 10 characters!

It's because the testCaseIDandDetailsBean.getMethodReplacement() method doesn't return a correct value.

EDIT: for your edited question (you should post another question and mark this answer as accepted instead of editing this one, but I'll answer anyway): The array you insert is bigger than the limit defined by the table. The table defines a VARRAY(20), so the problem is that you're trying to insert an array containing more than 20 elements. 2 solutions:

  • increase the limit in the table definition
  • make a smaller array
ElectronWill
  • 754
  • 9
  • 17