I am trying to read one excel sheet and then inserting it to the Database but while doing I am getting the ORA exception but when i manually tried the insert command it was working fine.
Below is the table schema :
Name Null Type
------------------- -------- ---------------------------------
LANGUAGE NOT NULL VARCHAR2(10 CHAR)
ZONE NOT NULL VARCHAR2(20 CHAR)
PRIORITY NOT NULL NUMBER(20,2)
PRODUCT_SEGMENT NOT NULL VARCHAR2(100 CHAR)
CUSTOMER_SUBTYPE VARCHAR2(50 CHAR)
MCC NOT NULL VARCHAR2(10 CHAR)
MNC NOT NULL VARCHAR2(10 CHAR)
CUSTOMER_TYPE NOT NULL VARCHAR2(5 CHAR)
TEMPLATE_WELKOM VARCHAR2(50 CHAR)
TEMPLATE_VOICE VARCHAR2(50 CHAR)
TEMPLATE_VOICE_FREE VARCHAR2(50 CHAR)
TEMPLATE_DATA VARCHAR2(50 CHAR)
TEMPLATE_DATA_FREE VARCHAR2(50 CHAR)
TEMPLATE_CAP VARCHAR2(50 CHAR)
TEMPLATE_TRAILER VARCHAR2(50 CHAR)
TEMPLATE_PROMO VARCHAR2(50 CHAR)
TEMPLATE_SMS VARCHAR2(50 CHAR)
SYS_CRE_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
SYS_UPD_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
TEMPLATE_PULL VARCHAR2(50 CHAR)
Below is the code:
package mosa.common.transaction;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
public class InapUnifyPushPull {
public static String unify_push_pull_insert="INSERT INTO Unify_push_pull_dummy(PRIORITY, PRODUCT_SEGMENT, CUSTOMER_TYPE, CUSTOMER_SUBTYPE, MCC, MNC, ZONE, LANGUAGE, TEMPLATE_PULL, TEMPLATE_WELKOM, TEMPLATE_VOICE, TEMPLATE_SMS, TEMPLATE_VOICE_FREE, TEMPLATE_DATA, TEMPLATE_DATA_FREE, TEMPLATE_CAP, TEMPLATE_PROMO, TEMPLATE_TRAILER, SYS_CRE_DATE, SYS_UPD_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,sysdate);";
public static Connection GetConeection() throws SQLException{
Connection con=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:@indsun063:1521:inad1", "M2M", "vodafone");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
@SuppressWarnings("rawtypes")
public static void InsertData(List sheetData) throws SQLException {
Connection con=null;
try {
con=GetConeection();
} catch (SQLException e) {
e.printStackTrace();
}
int priority=0;
String productSegment=null;
String customerType=null;
String customerSubtype=null;
String MCC=null;
String MNC=null;
String Zone=null;
String language=null;
String pull=null;
String welkom=null;
String voice=null;
String sms=null;
String voiceFree=null;
String Data=null;
String DataFree=null;
String dataCapping=null;
String Promo=null;
String Trailer=null;
for (int i = 1; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if(cell.getColumnIndex()==0){
if(cell.getCellType()==0){
priority=(int)cell.getNumericCellValue();
} else {
priority=Integer.parseInt(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==1){
if(cell.getCellType()==0){
productSegment=String.valueOf((int)cell.getNumericCellValue());
} else {
productSegment=cell.getStringCellValue();
}
}else if(cell.getColumnIndex()==2){
if(cell.getCellType()==0){
customerType=String.valueOf((int)cell.getNumericCellValue());
} else {
customerType=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==3){
if(cell.getCellType()==0){
customerSubtype=String.valueOf((int)cell.getNumericCellValue());
} else {
customerSubtype=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==4){
if(cell.getCellType()==0){
MCC=String.valueOf((int)cell.getNumericCellValue());
} else {
MCC=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==5){
if(cell.getCellType()==0){
MNC=String.valueOf((int)cell.getNumericCellValue());
} else {
MNC=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==6){
if(cell.getCellType()==0){
Zone=String.valueOf((int)cell.getNumericCellValue());
} else {
Zone=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==7){
if(cell.getCellType()==0){
language=String.valueOf((int)cell.getNumericCellValue());
} else {
language=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==8){
if(cell.getCellType()==0){
pull=String.valueOf((int)cell.getNumericCellValue());
} else {
pull=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==9){
if(cell.getCellType()==0){
welkom=String.valueOf((int)cell.getNumericCellValue());
} else {
welkom=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==10){
if(cell.getCellType()==0){
voice=String.valueOf((int)cell.getNumericCellValue());
} else {
voice=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==11){
if(cell.getCellType()==0){
sms=String.valueOf((int)cell.getNumericCellValue());
} else {
sms=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==12){
if(cell.getCellType()==0){
voiceFree=String.valueOf((int)cell.getNumericCellValue());
} else {
voiceFree=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==13){
if(cell.getCellType()==0){
Data=String.valueOf((int)cell.getNumericCellValue());
} else {
Data=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==14){
if(cell.getCellType()==0){
DataFree=String.valueOf((int)cell.getNumericCellValue());
} else {
DataFree=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==15){
if(cell.getCellType()==0){
dataCapping=String.valueOf((int)cell.getNumericCellValue());
} else {
dataCapping=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==16){
if(cell.getCellType()==0){
Promo=String.valueOf((int)cell.getNumericCellValue());
} else {
Promo=(cell.getStringCellValue());
}
} else if(cell.getColumnIndex()==17){
if(cell.getCellType()==0){
Trailer=String.valueOf((int)cell.getNumericCellValue());
} else {
Trailer=(cell.getStringCellValue());
}
}
}
//PreparedStatement stmt;
try {
PreparedStatement stmt=con.prepareStatement(unify_push_pull_insert);
System.out.println(priority +","+productSegment+","+customerType+","+customerSubtype+","+MCC+","+MNC+","+Zone+","+language+","+pull+","+welkom+","+voice+","+sms+","+voiceFree+","+Data+","+DataFree+","+dataCapping+","+Promo+","+Trailer);
stmt.setInt(1, priority);
stmt.setString(2, productSegment);
stmt.setString(3, customerType);
stmt.setString(4, customerSubtype);
stmt.setString(5, MCC);
stmt.setString(6, MNC);
stmt.setString(7, Zone);
stmt.setString(8, language);
stmt.setString(9, pull);
stmt.setString(10, welkom);
stmt.setString(11, voice);
stmt.setString(12, sms);
stmt.setString(13, voiceFree);
stmt.setString(14, Data);
stmt.setString(15, DataFree);
stmt.setString(16, dataCapping);
stmt.setString(17, Promo);
stmt.setString(18, Trailer);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.out.println();
con.close();
con=GetConeection();
PreparedStatement stmt=con.prepareStatement(unify_push_pull_insert);
stmt.setInt(1, priority);
stmt.setString(2, productSegment);
stmt.setString(3, customerType);
stmt.setString(4, customerSubtype);
stmt.setString(5, MCC);
stmt.setString(6, MNC);
stmt.setString(7, Zone);
stmt.setString(8, language);
stmt.setString(9, pull);
stmt.setString(10, welkom);
stmt.setString(11, voice);
stmt.setString(12, sms);
stmt.setString(13, voiceFree);
stmt.setString(14, Data);
stmt.setString(15, DataFree);
stmt.setString(16, dataCapping);
stmt.setString(17, Promo);
stmt.setString(18, Trailer);
stmt.executeUpdate();
}
}
}
}
Console Output :
No of rows : 95
60000,BUURLANDEN,I,,999,999,BELGIE_DUITSLAND,NL,,,1,1,,1,,,,
java.sql.SQLException: ORA-00911: invalid character
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at mosa.common.transaction.InapUnifyPushPull.InsertData(InapUnifyPushPull.java:213)
at mosa.common.file.read.main(read.java:83)
