0

I have table and CVS file what i want to do is from csv have to update the table.

csv file as follows (no delta)
1,yes
2,no
3,yes
4,yes

Steps through java

  1. what i have did is read the csv file and make two lists like yesContainList,noContainList in that list added the id values which has yes and no seperately

  2. make the list as coma seperated strinh

  3. Update the table with the comma seperated string

Its working fine. but if i want to handle lakhs of records means somewhat slow.

Could anyone tell whether is it correct way or any best way to do this update?

Alexander
  • 3,129
  • 2
  • 19
  • 33
jackyesind
  • 3,343
  • 14
  • 47
  • 74

2 Answers2

1

There are 2 basic techniques to do this:

  1. sqlldr
  2. Use an external table.

Both methods are explained here: Update a column in table using SQL*Loader?

Community
  • 1
  • 1
DS.
  • 604
  • 2
  • 6
  • 24
  • yes By your reply we have to read the csv file and insert into temp table from that temp table we have to update. I have question as what will we do in case of csv reading failure? how much recurd we can update from another table – jackyesind Mar 04 '14 at 06:23
  • 1
    You should check the data file is readable etc in your code (unix shell script normally) before you call sqlldr. sqlldr will automatically put any bad rows from the datafile in a .bad file, the good rows will still be loaded. The presence of a *.bad file means something has gone wrong. – DS. Mar 04 '14 at 09:50
  • After complete the work shall we need to delete the external table? before call once again the csv load – jackyesind Mar 04 '14 at 13:32
  • 1
    jackyesind You cannot insert or delete from an external table. An external table is a view of a file (at a specific location with a specific name). When you change the file the table contents automatically change. – DS. Mar 04 '14 at 14:59
1

Doing jobs like bulk operation, import, exports or heavy SQL operation is not recommended to be done outside RDBMS due to performance issues.

By fetching and sending large tables throw ODBC like API's you will suffer network round trips, memory usage, IO hits ....

When designing a client server application (like J2EE) do you design a heavy batch operation being called and controlled from user interface layer synchronously or you will design a server side process triggered by clients command?.

Think about your java code as UI layer and RDBMS as server side.
BTW RDBMS's have embedded features for these operations like SQLLOADER in oracle.

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
  • Its could be a completely off java work. PLSQL (Oarcle sql) have methods to use SQLLDR API. You may develop a procedure in plsql and call it by java. – Mohsen Heydari Mar 03 '14 at 12:57
  • See [hear](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8812348049) – Mohsen Heydari Mar 03 '14 at 12:59
  • by jdbc can i call this – jackyesind Mar 03 '14 at 13:03
  • Yes, [you can, see the sample](http://stackoverflow.com/questions/14900708/java-calling-a-stored-procedure-in-an-oracle-database) or [this one](http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/). – Mohsen Heydari Mar 03 '14 at 13:13
  • jackyesind, you can't update using sql loader, did you read my reply? – DS. Mar 03 '14 at 13:31
  • http://stackoverflow.com/questions/9091167/update-a-column-in-table-using-sqlloader by this sqlloader used for insert only. Is there any performance change between read from csv and read from temp table using sqlloader – jackyesind Mar 04 '14 at 06:24
  • Oracle provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.It seems performance will be the same. – Mohsen Heydari Mar 04 '14 at 06:51
  • please tell which approcah i have to do either using java csv read or sqloader and read data from temp file? – jackyesind Mar 04 '14 at 09:03
  • Depends on your applications logic. If file size is limited and the java approach performance is acceptable go with it, else you need the sqlloader solution. have a simple test to compare them. – Mohsen Heydari Mar 04 '14 at 09:11
  • file may have lakhs of record. thanks for ur reply. I will test and let u know – jackyesind Mar 04 '14 at 10:14