Error query sql oracle

Question

Error query sql oracle

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have a java application that is connected by JDBC and I make this query:

My code:

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 the error I get:

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

TB I've tried:

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

and the error:

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

However, I run the same query on my sql developer and it works

java oracle

asked by sirdaiz 19.05.2017 в 12:04

source

2 answers

0

This error is caused because the date is sent as string on the query, and the program expects to receive a numeric value, that is, a number, as a recommendation is that you change the date to a string or change the value received at string in this way you will receive the same data and should not skip the error. If you put more code I can give you a more detailed assessment.

answered by 19.05.2017 в 12:09

Give more time to show in ajax In MySQL how should the data types be declared in case of using the following elements in JAVA

score 2 · Accepted Answer

If it works in your SQL-Developer and not when you launch it through your application, it is because the session opened by SQL-Developer is configured with a different language and date format than the session you open from your application.

You can check the parameters of your session with the following query:

SELECT * FROM nls_session_parameters;

The information that will interest you is NLS_DATE_FORMAT and NLS_DATE_LANGUAGE , although if you did not know it, it is convenient that you take a look at the rest of the parameters ( NLS_LANGUAGE , NLS_NUMERIC_CHARACTERS , etc.)

In your case, when you compare or convert chains to date, you have to take into account this configuration.

You comment for example that when you launch this query, you receive an error that the month is not valid:

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

This is because Dec is the acronym for Dec ember and if it works for you in SQL-Developer, it is because NLS_DATE_LANGUAGE is set to display the names of the months in English ( AMERICAN ).

If it does not work for you in your application, it is because NLS_DATE_LANGUAGE will have it set to show the names of the months in Spanish ( SPANISH ), so you really should have written 21- Dic -16 (from Dec December).

These values can be modified with the ALTER SESSION SET Option=Value command or you can ask your DBA to configure it for you. I leave you a link .