java.sql.SQLException: ORA-01036: illegal variable name/number

Question

I've got a weird problem in Oracle query running from Java with Oracle 11g. The same query worked good with Oracle 10g. tool_availability is a view.

final String SELECT_SQL = "select * " +
            "from tool_availability " +
            "where tool_code = ? " +
            "and tool_number = ? " +
            "and unit = ? " +
            "and part_id = ? ";

ps = con.prepareStatement(SELECT_SQL);
ps.setFetchDirection(ResultSet.FETCH_FORWARD);
ps.setFetchSize(1);
ps.setString(1, tool_code);
ps.setString(2, tool_number);
ps.setString(3, unit);
ps.setString(4, part_id);
rs = ps.executeQuery();

The query has space before and after '?' mark. The same query is not working with 11g and I'm getting below error:

java.sql.SQLException: ORA-01036: illegal variable name/number

If I remove the space before and after '?' mark. The same query is working with 11g.

Could anybody give some insight what could be the problem?

My guess is it is the difference in JDBC. Are you importing the ORACLE JDBC driver or the generic one? Are you importing a different driver based upon Oracle version? I would look there. — FlyingGuy
– FlyingGuy, Commented Dec 19, 2014 at 6:29

Siva Kumar · Accepted Answer · 2014-12-15 12:41:01Z

1

You have to avoid to set String in preparedstatement.

In preparedStatement , you need to set actual type.

ps.setString(1, tool_code);
ps.setInt(2, tool_number);
ps.setInt(3, unit);
ps.setString(4, part_id);

like that you have to do

answered Dec 15, 2014 at 12:41

Siva Kumar

2,0063 gold badges15 silver badges28 bronze badges

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

1 Comment

Raghav Over a year ago

Thanks for your response. The datatype for all columns are VARCHAR2.

Collectives™ on Stack Overflow

java.sql.SQLException: ORA-01036: illegal variable name/number

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related