permaculture playing cards*
The moose likes JDBC and the fly likes java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric " Watch "java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric " New topic
Author

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

Mohan Samikkannu
Greenhorn

Joined: Jul 10, 2006
Posts: 2
Hi,
please help me to resolve the issue
while trying to insert the date field to the database iam getting

Error
------------------------
"java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected"

table
------------------------------------------------------
CREATE TABLE APP_EMPTYPE (
ET_APPLICATION_NAME VARCHAR2 (20) NOT NULL,
ET_NP_USERS VARCHAR2 (100),
ET_AC_USERS VARCHAR2 (100),
ET_CREATION_DATE DATE,
ET_MODIFY_DATE DATE,
ET_APP_ID VARCHAR2 (10),
PRIMARY KEY ( ET_APPLICATION_NAME ) ;



method called in java
-------------------------

String insertQuery = "INSERT INTO APP_EMPTYPE (ET_APPLICATION_NAME,ET_NP_USERS,ET_AC_USERS,ET_CREATION_DATE,ET_MODIFY_DATE) VALUES (?,?,?,?,?) ";
pStatement = conFastDB.prepareStatement(insertQuery);

pStatement.setString(1,appUserCon.getMAppName());
pStatement.setString(2,appUserCon.getMNPUsers());
pStatement.setString(3,appUserCon.getMACUsers());

String strDt = "";
SimpleDateFormat formatter = null;
Calendar nowDate = Calendar.getInstance();

formatter = new SimpleDateFormat("dd/MM/YYYY");
strDt = formatter.format(nowDate.getTime());
System.out.println("strDt====getDate===>"+strDt);


pStatement.setString(4,"to_char(to_date('"+strDt+"','DD/MM/YYYY'))");
pStatement.setString(5,"to_char(to_date('"+strDt+"','DD/MM/YYYY'))");
intRtn_cnt = pStatement.executeUpdate();


Regards
Mohan Samikkannu
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


pStatement.setString(4,"to_char(to_date('"+strDt+"','DD/MM/YYYY'))");
pStatement.setString(5,"to_char(to_date('"+strDt+"','DD/MM/YYYY'))");

Try using the setDate() method of PreparedStamement. Then you won't need to use to_char or to_date.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Paul's response is exactly right, but he didn't explain why...

With the PreparedStatement.setXXX() methods, you can only set database values, the actual database datatype things. To make a very bad analogy to Java, it's sort of like a function that could only except java primitives and no Object types.

Another way of explaining it is that the database does not do string replacement into the SQL within the set methods; instead, the data value is bound to an already parsed SQL statement that has been converted into an internal object format within the database. No further interpretation of SQL is going to take place after connection.prepareStatement().

There are a very very very few drivers for which the above is not true and what you tried might have worked, but not for any of the major databases.
Mohan Samikkannu
Greenhorn

Joined: Jul 10, 2006
Posts: 2
Thanks a lot for the timely reply
Girish kumar.v
Greenhorn

Joined: Nov 17, 2009
Posts: 8
HI frens, i'm trying to insert multiple rows into oracle database,
the rows are in Excel sheet ,i'm fetching it and trying it to insert into database
but i'm getting a error as follows

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


i came to know that the error is all due to the date format
i'm attaching the part of the code so that you can go through and lemme know wat is the problem in it.

the code is as follows
package com.r2k;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import jxl.Cell;
import java.util.Date;

public class DBInsert {
public static void main(String[] args)
{
try{
//Date row9Date=null;
//Date row15Date=null;
String filename="C:/input_1.xls";
jxl.Workbook workbook = jxl.Workbook.getWorkbook(new File(filename));
jxl.Sheet sheet=workbook.getSheet(0);
int noOfColumns=sheet.getColumns();
int noOfRowsInSheet=sheet.getRows();
System.out.println("No of columns:"+noOfColumns);
System.out.println("No of rows:"+noOfRowsInSheet);

System.out.println("connecting....");
//DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("driver loaded");
Connection conn = DriverManager.getConnection("jdbcracle:thin:@//192.168.2.116:1521/test_ka", "madhu", "madhu");
System.out.println("database connection succeded");
String header[]=new String[noOfColumns];
Cell headerCell[]=new Cell[noOfColumns];
int k=1;
for(int i=0;i<noOfColumns;i++)
{
headerCell[i] = sheet.getCell(i,0);
header[i]=headerCell[i].getContents();
//System.out.println(header[i]);
}
Cell row[]=new Cell[noOfColumns];
String rowContents[]=new String[noOfColumns];
for(int i=0;i<noOfRowsInSheet;i++)
{
row=sheet.getRow(i);
for(int j=0;j<noOfColumns;j++)
{
rowContents[j]=row[j].getContents();
//System.out.println(rowContents[j]+"\t");
}
String FILEPATH=rowContents[0];
String FILENAME =rowContents[1];
String DOCUMENT_TYPE =rowContents[2];
String LOCATION =rowContents[3];
String CONNECTION_TYPE =rowContents[4];
String CUSTOMER_NAME =rowContents[5];
String CUSTOMER_CODE =rowContents[6];
String MOBILE_PREFIX =rowContents[7];
String MOBILE_NUMBER =rowContents[8];
String ADATE=rowContents[9];;
Date ACTIVATION_DATE=null;
DateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm");
if(ADATE.equals("")||ADATE==null)
{
ACTIVATION_DATE=null;
}
else
{
try
{

ACTIVATION_DATE = df.parse(ADATE);
System.out.println("Today = " + df.format(ACTIVATION_DATE));
}
catch (ParseException e)
{
e.printStackTrace();
}
}
//rowContents[9];
String PAN =rowContents[10];
String PREV_CUSTOMER_CODE=rowContents[11];
String PRMOBILE_PREFIX =rowContents[12];
String PRMOBILE_NUMBER =rowContents[13];
String REMARKS =rowContents[14];
Date SCANNED_DATE=null;
DateFormat df1 = new SimpleDateFormat("dd/MM/yyyy HH:mm");

String SDATE=rowContents[15];
if(SDATE.equals("")||SDATE==null)
{
SCANNED_DATE=null;
}
else{


try
{
SCANNED_DATE = df1.parse(SDATE);

}
catch (ParseException e)
{
e.printStackTrace();
}

}
String CAF_NUMBER =rowContents[16];
String REVISION =rowContents[17];
Statement stmt = conn.createStatement ();

String Query="insert into drawings4 values('"+FILEPATH+"','"+FILENAME+"','"+ DOCUMENT_TYPE+"','"+CONNECTION_TYPE+"','" +CUSTOMER_NAME+"','"+ CUSTOMER_CODE+"','"+ MOBILE_PREFIX+"','"+ MOBILE_NUMBER+"','"+
ACTIVATION_DATE+"','"+ PAN+"','"+ PREV_CUSTOMER_CODE+"','" +PRMOBILE_PREFIX+"','"+PRMOBILE_NUMBER+"','"+
REMARKS+"','"+ SCANNED_DATE+"','" +CAF_NUMBER+"','"+ REVISION+ "',"+(k++)+",'"+ LOCATION+"')";


//String Query="insert into drawings1 values('"+FILEPATH+"','"+FILENAME+"','"+ DOCUMENT_TYPE+"','PREPAID','" +CUSTOMER_NAME+"','"+ CUSTOMER_CODE+"','"+ MOBILE_PREFIX+"','"+ MOBILE_NUMBER+"','"+
//ACTICATION_DATE+"','"+ PAN+"','"+ PREV_CUSTOMER_CODE+"','" +PRMOBILE_PREFIX+"','"+PRMOBILE_NUMBER+"','"+
//REMARKS+"','"+ SCANNED_DATE+"','" +CAF_NUMBER+"','"+ REVISION+ "',"+0+",'"+ LOCATION+"')";




System.out.println("Query : "+Query);
try{
int noOfRows=stmt.executeUpdate(Query);
System.out.println(noOfRows);
}catch(SQLException e)
{
e.printStackTrace();
}
stmt.close();
}

conn.commit();
System.out.println("Commit complete");
conn.close();
System.out.println("connection closed");
}

catch (FileNotFoundException e)
{
System.out.println("File not found");
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
System.out.println(e.getMessage());
}
catch (Exception e)
{
System.out.println("hi \"Girish\" you have a new exception");
e.printStackTrace();
System.out.println(e.getMessage());
}
}

}


Thanks and regards
Girish Kumar V

/*please help me in getting rid of these bug */
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30356
    
150

Girish,
That's a lot of code. Do you have a smaller test case that illustrates the problem? Especially what is being passed to the SQL string.

Also, I recommend you use a PreparedStatement. This will get rid of date format issues.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Girish kumar.v
Greenhorn

Joined: Nov 17, 2009
Posts: 8
okay i will explain the thing in more detail ...
the date format in the xls sheet is as dd/mm/yyyy hh:mm
i'm reading it as a string
now i'm trying to convert it to date as shown below
for better understanding i'll comment those points

//read content stored as string in ADATE
String ADATE=rowContents[9];
Date ACTIVATION_DATE=null;
DateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm"); //this is the format i need to put in database
if(ADATE.equals("")||ADATE==null)
{
ACTIVATION_DATE=null;
}
else
{
try
{
ACTIVATION_DATE = df.parse(ADATE);
System.out.println("Today = " + df.format(ACTIVATION_DATE));
}
catch (ParseException e)
{
e.printStackTrace();
}
}

the insert query is below
Query :
insert into drawings4 values('','','CAF Form','pre-paid','A SHIRL MARIA','','98861','87361','null','null','','','','','Wed Jul 02 18:30:00 IST 4','','100',51,'Bangalore')
the problem is that it is not getting converted
also the default format in the oracle is noy the same

Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
As previous posts say that you should really use PreparedStatement and setDate method.

Problem : Oracle is trying to convert your date string to date using default date format and it expects a number but founds text.
Solution : Again, use PreparedStatement and setDate method.

Regards,

Fatih.
Girish kumar.v
Greenhorn

Joined: Nov 17, 2009
Posts: 8
hi

i used the prepared statement as per you suggestion but din help it out

my problem is in converting the fetched string into date(the string format is "dd/mm/yyyy hh:mm")
how do i convert the following with the same format to be retained so that i can insert into the DB
i've also attached my code and the fragment

Thanks girish
Prabhat Jha
Ranch Hand

Joined: Aug 13, 2007
Posts: 58
in SimpleDateFormat pass the same format as used by your database.

Thanks


Thanks,
Prabhat
SCJP 1.5, SCWCD 1.5, SCBCD 1.5
shareeef hiasat
Greenhorn

Joined: Aug 27, 2013
Posts: 3
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric