CODEWITHSUNDEEP
javaplsql
Piyush Vinchurkar
Piyush Vinchurkar

Reputation: 17

Non-numeric character was found where a numeric was expected

I'm getting error as

ORA-01858: a non-numeric character was found where a numeric was expected

for a query in Eclipse but the same query is running fine in Toad.

I tried changing the date to to_char.

String query3 = "SELECT TXN_DATETIME FROM S3_ANTI_MONEY_LAUNDERING_TDS WHERE TRUNC(ROW_LOADED_DATE_TIME)='30-OCT-2018' AND SRC_FEED_NUM='63' AND BANK_ID IN('ISPP') AND SRC_RECORD_NUM IN('3','6')";

statement = connection.createStatement();
rs3 = statement.executeQuery(query3);

ArrayList<String> resultList1 = new ArrayList<String>();
while (rs3.next()) {
    String result = rs3.getString(1) ;
    resultList1.add(result);

Upvotes: 0

Views: 1604

Answers (2)

Tad Harrison
Tad Harrison

Reputation: 1293

The problem is likely the way that you have the date specified.

If you do not provide a proper date literal, then Oracle is using your NLS_DATE_FORMAT for the implicit conversion from text to date, and this might not be what you expect.

Replace this:

String query3 = "SELECT TXN_DATETIME FROM S3_ANTI_MONEY_LAUNDERING_TDS WHERE TRUNC(ROW_LOADED_DATE_TIME)='30-OCT-2018' AND SRC_FEED_NUM='63' AND BANK_ID IN('ISPP') AND SRC_RECORD_NUM IN('3','6')";

With this:

String query3 = "SELECT TXN_DATETIME FROM S3_ANTI_MONEY_LAUNDERING_TDS WHERE TRUNC(ROW_LOADED_DATE_TIME) = DATE '2018-10-30' AND SRC_FEED_NUM='63' AND BANK_ID IN('ISPP') AND SRC_RECORD_NUM IN('3','6')";

The proper way to do a DATE literal in Oracle is one of these:

DATE 'yyyy-mm-dd'

TIMESTAMP 'yyyy-mm-dd hh24:mi:ss'

Of course, you can use whatever mask you wish in this syntax:

TO_DATE('2018-10-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

Oracle docs for NLS_DATE_FORMAT

Oracle docs for DATE and DATETIME literals

Finally, if your code is building that query on the fly, it's much better to use bind variables and a prepared statement. Just make sure you are familiar with the nuances of Java dates and timestamps vs. Oracle dates and timestamps.

Upvotes: 1

Joseph Larson
Joseph Larson

Reputation: 9058

Without seeing your database schema, I'd say this looks fishy:

SRC_RECORD_NUM IN('3','6')

I personally would NEVER declare a character field with a _NUM suffix, but your IN clause is passing character data. I bet you need to do:

SRC_RECORD_NUM IN (3,6)

But I'm guessing based entirely on the name of the field.

Upvotes: 0

Related Questions