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

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

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.

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.

Thanks a lot for the timely reply

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("jdbc

racle: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 */

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.

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

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.

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

in SimpleDateFormat pass the same format as used by your database.

Thanks