insert values into table using concatenation and Varray

Question

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'.

 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(2,testCaseIDandDetailsBean.getClass_name()) ; ps.setString(3,testCaseIDandDetailsBean.getMethod_name()) ; ps.setString(4,testCaseIDandDetailsBean.getMethodReplacement()) ; ((OraclePreparedStatement)ps).setARRAY (5, newArray); ps.execute ();

Iam trying to execute this code but again Iam getting errors as follows:

 java.sql.SQLException: Invalid column index at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setARRAYInternal(OraclePreparedStatement.java:5906) at oracle.jdbc.driver.OraclePreparedStatement.setARRAY(OraclePreparedStatement.java:5898) at implementation.TestCaseIDandDetailsDAOImpl.addTestCaseIDandDetails(TestCaseIDandDetailsDAOImpl.java:54) at implementation.TestCaseIDandDetailsDAOImpl.main(TestCaseIDandDetailsDAOImpl.java:134)

my tablescripts 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 ;

but after these modifications I am getting error as bellow: ORA-12899: value too large for column "ER706221"."TESTCASEIDDETAILS"."METHODREPLACEMENT" (actual: 10, maximum: 2) — Nagaumadevi Palaparthy
– Nagaumadevi Palaparthy, Commented Oct 30, 2015 at 7:58

janos · Accepted Answer · 2015-10-30 06:26:33Z

3

The indexes should correspond to the indexes of the question mark placeholders in the prepared statement, not to the column numbers in the table. The index of the first question mark is 1, the second is 2, and so on. Your indexes are all off by one, should be 1 2 3 4 instead of 2 3 4 5.

answered Oct 30, 2015 at 6:26

janos

126k31 gold badges242 silver badges253 bronze badges

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

2 Comments

Nagaumadevi Palaparthy Over a year ago

but after these modifications I am getting error as bellow: ORA-12899: value too large for column "ER706221"."TESTCASEIDDETAILS"."METHODREPLACEMENT" (actual: 10, maximum: 2)

janos Over a year ago

Verify the value of testCaseIDandDetailsBean.getMethodReplacement(). It seems the value is not what you expect: a string of length 10

SpringLearner · Accepted Answer · 2015-10-30 06:27:11Z

1

Change the following

ps.setString(2,testCaseIDandDetailsBean.getClass_name()) ; ps.setString(3,testCaseIDandDetailsBean.getMethod_name()) ; ps.setString(4,testCaseIDandDetailsBean.getMethodReplacement()) ; ((OraclePreparedStatement)ps).setARRAY (5, newArray);

to

ps.setString(1,testCaseIDandDetailsBean.getClass_name()) ; ps.setString(2,testCaseIDandDetailsBean.getMethod_name()) ; ps.setString(3,testCaseIDandDetailsBean.getMethodReplacement()) ; ((OraclePreparedStatement)ps).setARRAY (4, newArray);

answered Oct 30, 2015 at 6:27

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

6 Comments

SpringLearner Over a year ago

@NagaumadeviPalaparthy please dont change the question other wise my answer will be void. Keep the original question and post another question for your new problem.

Nagaumadevi Palaparthy Over a year ago

sry I had put it back to original question

Nagaumadevi Palaparthy Over a year ago

but after these modifications I am getting error as bellow: ORA-12899: value too large for column "ER706221"."TESTCASEIDDETAILS"."METHODREPLACEMENT" (actual: 10, maximum: 2)

SpringLearner Over a year ago

@NagaumadeviPalaparthy earlier your error was different and this time your error is different. So this means this is a different question

Nagaumadevi Palaparthy Over a year ago

yes its different question but i cant post until 3 hrs and i need to do it immediately can u help me please?/

|

Collectives™ on Stack Overflow

insert values into table using concatenation and Varray

2 Answers 2

2 Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Related