oracle-databaseduplicatessqlexception
ORA-00957: duplicate column name while inserting data from excel to Oracle DB
This program reads data from excel with the following columns in a specific order:
Number, Short description, Priority, State, Category, Subcategory, Configuration item, Assignment group, Opened, Updated, Opened by, Resolution notes, Resolution Error, Resolution Type, Pending Reason, Event End Time, Event Start Time, Active, Closed, Comments and Work notes ,Created, Created by, Impact, Incident Start Time, Incident state, Opened by, Assignment Group, Resolve time, Resolved, Resolved Date, Resolved Time, Resolved at Level, Severity, LOB, Contact type
private static SimpleDateFormat inputSdf=new SimpleDateFormat("dd-MMM-yyyy");//MM/dd/yyyy hh:mm:ss aaa
private static SimpleDateFormat outputSdf=new SimpleDateFormat("yyyy-MM-dd");
private static String query=null;
private static String checkQuery=null;
private static String delQuery=null;
private static String maxIdQuery=null;
static{
String tableName="SUPPORT_NEW";
StringBuffer buffer=new StringBuffer();
buffer.append("INSERT INTO "+tableName+" ( ");
buffer.append(" SPRT_ID, SPRT_NBR, SPRT_DESC, PRTY_TXT, ST_TXT, ");
buffer.append(" CTGY_TXT, SCTGY_TXT, CONF_ITEM_TXT, ASGMT_GRP_TXT, OPENED_TMSTP, " );
buffer.append(" UPDATED_TMSTP, OPENED_BY_TXT, RES_NOTE_TXT, RES_ERROR_TXT, RES_TYPE_TXT, ");
buffer.append(" PEND_RESN_TXT, EVENT_END_TM, EVENT_START_TM, ACT_TXT, ");
buffer.append(" CLOSED_TMSTP, COM_AND_WORK_NOTE_TXT, CREATED_TM, CREATED_BY_TXT, IMPACT_TXT, ");
buffer.append(" INCDT_START_TM, INCDT_ST_TXT, OPEN_BY_GRP_TXT, RESLV_TM, RESLV_DT, "); //RESOLVED=RESLV_DT
buffer.append(" RESLV_TM, RESLV_AT_LEVEL_TXT, SVRTY_TXT, LOB_TXT, "); //Resolved_Date=null
buffer.append(" SLA_TXT, MONIT_TYPE_TXT ");
buffer.append(") VALUES (");
buffer.append(" ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?");
buffer.append(") ");
query=buffer.toString();
checkQuery = " SELECT SPRT_ID FROM "+tableName+" WHERE SPRT_NBR = ? ";
delQuery = " DELETE FROM "+tableName+" WHERE SPRT_NBR = ? ";
maxIdQuery = " SELECT MAX(SPRT_ID) FROM "+tableName;
}
public static void insertToDB(String[][] data) throws Exception{
Connection dbConnection=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConnection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.7.39:1521:ORCL","DASHBOARD","DASHBOARD");
PreparedStatement preparedStatement = dbConnection.prepareStatement(query);
PreparedStatement checkStatement = dbConnection.prepareStatement(checkQuery);
PreparedStatement delStatement = dbConnection.prepareStatement(delQuery);
PreparedStatement maxIdStatement = dbConnection.prepareStatement(maxIdQuery);
ResultSet rs1;
ResultSet rs2;
long id=0;
for (int i=1;i<data.length;i++) {
String col[]=data[i];
System.out.print("\n Preparing Record "+i+"@= PDMS_ID="+col[0]+";id="+id);
checkStatement.setString(1, col[0]);
rs1=checkStatement.executeQuery();
if(rs1.next()){
delStatement.setString(1, col[0]);
delStatement.executeUpdate();
System.out.print("=>ID Exists hence updating ");
}else{
System.out.print("=>Fresh record ");
}
System.out.println();
rs2=maxIdStatement.executeQuery();
while(rs2.next()){
id=rs2.getLong(1);
}
id++;
preparedStatement.setLong(1, id); //id
preparedStatement.setString(2, col[0]); //pdsm_number
((OraclePreparedStatement) preparedStatement).setStringForClob(3, col[1]);//Description
preparedStatement.setString(4, col[2]); //Priority
preparedStatement.setString(5, col[3]); //State
preparedStatement.setString(6, col[4]); //Category
preparedStatement.setString(7, col[5]); //Subcategory
preparedStatement.setString(8, col[6]); //Configuration_item
preparedStatement.setString(9, col[7]); //Assignment_group
//preparedStatement.setDate(10, (java.sql.Date)sdf.parse(col[9])); //Opened
((OraclePreparedStatement) preparedStatement).setDate(10, formateDate(col[8]));
((OraclePreparedStatement) preparedStatement).setDate(11, formateDate(col[9])); //Updated
preparedStatement.setString(12, col[10]); //Opened_by
((OraclePreparedStatement)preparedStatement).setStringForClob(13, col[11]);//Resolution_notes
preparedStatement.setString(14, col[12]); //Resolution_Error
preparedStatement.setString(15, col[13]); //Resolution_Type
preparedStatement.setString(16, col[14]); //Pending_Reason
((OraclePreparedStatement) preparedStatement).setDate(17, formateDate(col[15]));; //Event_End_Time
((OraclePreparedStatement) preparedStatement).setDate(18, formateDate(col[16]));; //Event_Start_Time
//preparedStatement.setString(19, col[18]); //Action
preparedStatement.setString(19, col[17]); //Active
((OraclePreparedStatement) preparedStatement).setDate(20, formateDate(col[18]));; //Closed
((OraclePreparedStatement)preparedStatement).setStringForClob(21, col[19]);//Comments_and_Work_notes
((OraclePreparedStatement) preparedStatement).setDate(22, formateDate(col[20]));; //Created
preparedStatement.setString(23, col[21]); //Created_by
preparedStatement.setString(24, col[22]); //Impact
((OraclePreparedStatement) preparedStatement).setDate(25, formateDate(col[23])); //Incident_Start_Time
preparedStatement.setString(26, col[24]); //Incident_state
preparedStatement.setString(27, col[25]); //Opened_by_Assignment_Group
preparedStatement.setString(28, col[26]); //Resolve_time
((OraclePreparedStatement) preparedStatement).setDate(29, formateDate(col[27])); //Resolved
//preparedStatement.setString(30, null); //Resolved_Date
preparedStatement.setString(30, col[29]); //Resolved_Time
preparedStatement.setString(31, col[30]); //Resolved_at_Level
preparedStatement.setString(32, col[31]); //Severity
if("APAC Supply Chain".equalsIgnoreCase(col[32].trim())){ //LOB
preparedStatement.setString(33, "APAC");
}else{
preparedStatement.setString(33, "US");
}
//SLA
if(col[15]!=null && !col[15].trim().equals("") && col[23]!=null && !col[23].trim().equals("")){
long interval=formateDate(col[15]).getTime()- formateDate(col[23]).getTime();
long p1 = 60*60*1000;//P1 – 1 Hour
long p2 = 2*60*10*1000;//P2 – 2 Hours
long p3 = 24*60*10*1000;//P3 – 24 Hours
long p4 = 21*24*60*10*1000;//P3 – 21 days
if("1-Critical".equalsIgnoreCase(col[2])){//P1
preparedStatement.setString(34, (interval>p1)?"BREACHED":"ADHERED");
}else if("2-High".equalsIgnoreCase(col[2])){//P2
preparedStatement.setString(34, (interval>p2)?"BREACHED":"ADHERED");
}else if("3-Moderate".equalsIgnoreCase(col[2])){//P3
preparedStatement.setString(34, (interval>p3)?"BREACHED":"ADHERED");
}else if("4-Low".equalsIgnoreCase(col[2])){//P4
preparedStatement.setString(34, (interval>p4)?"BREACHED":"ADHERED");
}
}else{
preparedStatement.setString(34, "ADHERED");
}
if(col[33].equalsIgnoreCase("Monitoring")){//MONITOR_TYPE
preparedStatement.setString(35, "System Generated");
}else{
preparedStatement.setString(35, "User Generated");
}
preparedStatement.execute();
/*preparedStatement.addBatch();
if (i % 100 == 0 || (i+1) == data.length) {
System.out.println("Batch update @"+i);
preparedStatement.executeBatch(); // Execute every 100 items.
}*/
}
} catch (Exception ex) {
throw ex;
} finally{
try{
if(dbConnection!=null && !dbConnection.isClosed()){
dbConnection.close();
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}
public static void main(String[] args) {
try{
System.out.println("Reading Data from XLS");
List<String[][]> xlsData=ReadXLGeneric.readExcelData("D:/DASHBOARD_WORKSPACE/data/latest-pdsm.xlsx");
//"D:/Duke/0/projects/vmo/dbDump/PDSMDumps/PDSMData/Feb.xlsx
System.out.println("Inserting into DB");
insertToDB(xlsData.get(0));
}catch(Exception e){
e.printStackTrace();
}
}
public static java.sql.Date formateDate(String input)throws Exception{
try {
if(input!=null && !input.equals("") && !input.equals("UNDEFINED")){
return java.sql.Date.valueOf(outputSdf.format(inputSdf.parse(input)));
}
return null;
} catch (ParseException e) {
throw e;
}
}
public static List<String[][]> readExcelData(String fileName) {
List<List<List<String>>> sheetList=new ArrayList<List<List<String>>>();
List<List<String>> strTable=new ArrayList<List<String>>();
List<String> strList=new ArrayList<String>();
Cell cell=null;
try {
FileInputStream fis = new FileInputStream(fileName);
Workbook workbook = null;
if(fileName.toLowerCase().endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.toLowerCase().endsWith("xls")){
workbook = new HSSFWorkbook(fis);
}else{
throw new IllegalArgumentException("Invalid formate should be xls or xlsx");
}
//loop through each of the sheets
for(int i=0; i < workbook.getNumberOfSheets(); i++){
Sheet sheet = workbook.getSheetAt(i);
Iterator<Row> rowIterator = sheet.iterator();
strTable=new ArrayList<List<String>>();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
strList=new ArrayList<String>();
while (cellIterator.hasNext())
{
cell=cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
strList.add("");
break;
case Cell.CELL_TYPE_STRING:
strList.add(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if(cell.toString().contains("-")){
strList.add(cell.toString());
}else{
strList.add(Double.toString(cell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_FORMULA:
//System.out.println("Formula is " + cell.getCellFormula());
switch(cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
//System.out.println("Last evaluated as: " + cell.getNumericCellValue());
strList.add(Double.toString(cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
// System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
strList.add(cell.getRichStringCellValue().toString());
break;
}
break;
default:
strList.add("UNDEFINED");
}
}
strTable.add(strList);
}
sheetList.add(strTable);
}
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
return getAsSheetList(sheetList);
}
On running this program I get the following exception
Below is the database model in which data is to be transferred .
How to resolve this?
Solution
In you INSERT INTO buffer.append you use two times the name RESLV_TM.
buffer.append(" INCDT_START_TM, INCDT_ST_TXT, OPEN_BY_GRP_TXT, RESLV_TM, RESLV_DT, "); //RESOLVED=RESLV_DT
buffer.append(" RESLV_TM, RESLV_AT_LEVEL_TXT, SVRTY_TXT, LOB_TXT, "); //Resolved_Date=null
As db told you, you can't use two times the same column name in INSERT command.
You need to change one of them