What does the following Oracle error mean invalid column index

Question

I got the following error while testing some code       SQLException  Invalid column index   What exactly does that mean    Is there an online document explaining what all the Oracle error codes and statements

User · Answer

the final sql statement is something like:

select col_1 from table_X where col_2 = 'abcd';

i run this inside my SQL IDE and everything is ok.

Next, i try to build this statement with java:

String queryString= "select col_1 from table_X where col_2 = '?';";
PreparedStatement stmt = con.prepareStatement(queryString);
stmt.setString(1, "abcd"); //raises java.sql.SQLException: Invalid column index

Although the sql statement (the first one, ran against the database) contains quotes around string values, and also finishes with a semicolumn, the string that i pass to the PreparedStatement should not contain quotes around the wildcard character ?, nor should it finish with semicolumn.

i just removed the characters that appear on white background

"select col_1 from table_X where col_2 = ' ? ' ; ";

to obtain

"select col_1 from table_X where col_2 = ?";

(i found the solution here: https://coderanch.com/t/424689/databases/java-sql-SQLException-Invalid-column)

User · Answer

Using Spring s SimpleJdbcTemplate  I got it when I tried to do this   String sqlString    select pwy code from approver where university id    123    List lt Map lt String  Object gt  gt  rows   getSimpleJdbcTemplate   queryForList sqlString  uniId      I had an argument to queryForList that didn t correspond to a question mark in the SQL   The first line should have been   String sqlString    select pwy code from approver where university id

User · Answer

I also got this type error  problem is wrong usage of parameters to statement like  Let s say  you have a query like this  SELECT   FROM EMPLOYE E WHERE E ID       and for the preparedStatement object  JDBC  if you set the parameters like  preparedStatement setXXX 1 value   preparedStatement setXXX 2 value    then it  results  in       SQLException  Invalid column index  So  I removed that second parameter setting to prepared statement then problem solved

User · Answer

I had this problem in one legacy application that create prepared statement dynamically   String firstName  StringBuilder query  new StringBuilder  select id  name from employee where country Code 1    query append  and  name like      query append firstName          query append  and ssn      PreparedStatement preparedStatement  new prepareStatement query toString       when it try to set value for ssn  it was giving invalid column index error  and finally found out that it is caused by firstName having   within  that disturb the syntax

User · Answer

If that s a SQLException thrown by Java  it s most likely because you are trying to get or set a value from a ResultSet  but the index you are using isn t within the range    For example  you might be trying to get the column at index 3 from the result set  but you only have two columns being returned from the SQL query

User · Answer

I had the exact same problem when using Spring Security 3 1 0  and Oracle 11G   I was using the following query and getting the invalid column index error    lt security jdbc-user-service data-source-ref  dataSource                  users-by-username-query  SELECT A user name AS username  A password AS password FROM MB REG USER A where A user name lower       It turns out that I needed to add   1 as enabled  to the query     lt security jdbc-user-service data-source-ref  dataSource  users-by-username query  SELECT A user name AS username  A password AS password  1 as enabled FROM MB REG USER A where A user name lower       Everything worked after that   I believe this could be a bug in the Spring JDBC core package

User · Answer

It sounds like you re trying to SELECT a column that doesn t exist    Perhaps you re trying to ORDER BY a column that doesn t exist   Any typos in your SQL statement

User · Answer

I had this problem using a prepared statement  I didn t add enough     for the  VALUES    My eclipse had crashed after I did add the proper amount  and lost those changes  But that didn t occur to me to be the error until I started combing through the SQL as p campbell suggested

[java] What does the following Oracle error mean: invalid column index

Examples related to java

Examples related to sql

Examples related to oracle