CODEWITHSUNDEEP
javaoracle-database
sirdaiz
sirdaiz

Reputation: 260

Error in sql oracle

I have App Java and its connect for JDBC and execute this query:

String date = '21-Dec-16';
StringBuilder query = new StringBuilder("SELECT * ");
query.append("FROM TEST WHERE PUBLISHED_DATE='").append(date).append("'");
connection = getConnection(jdbc);
stmt = connection.createStatement();
rs = stmt.executeQuery(query.toString());

syso query => select * from TEST where PUBLISHED_DATE='21-Dec-16'

and error:

java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

i too test:

select * from TEST where PUBLISHED_DATE=TO_DATE('21-Dec-16','DD-MON-RR')

and error:

java.sql.SQLDataException: ORA-01843: not a valid month

Nevertheless, i execute this query in my sql developer and works!

Upvotes: 0

Views: 72

Answers (2)

Spock
Spock

Reputation: 327

If you want to use that format for dates, you have to change the language settings of your database session:

Statement s = connection.createStatement();
s.execute("alter session set NLS_LANGUAGE = 'AMERICAN'");

and then you can use the TO_DATE function as in your second example.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

The immediate cause of your error is that you are using a date format which is incorrect for an Oracle query. But that aside, you should seriously consider using a prepared statement here. In addition to protecting your program against SQL injection, it will also relieve you from worrying about how to format your date for the query. Instead, you can simply bind the direct Java date object to the query.

String query = "SELECT * FROM TEST WHERE PUBLISHED_DATE = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setDate(1, date);    // here 'date' is an actual Date object, not a string
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    // access each record
}

If you absolutely must continue down your current road, I would expect the following raw query to run without error:

SELECT * FROM TEST WHERE PUBLISHED_DATE = '2016-12-21'

You could use a Java SimpleDateFormat object to obtain this from a Date object.

Upvotes: 1

Related Questions