Exception handling

[annapoorna ch]

Hi

While running batch jobs i am trying to retrieve some data from one table consists of lacks of records and insert into another table like this
st.executeUpdate("INSERT INTO Table2(source_id,period) SELECT Table1 .RESOURCE_ID, Table1 .period FROM Table1");

but the issue is if exception occurs while inserting a record then the whole job is getting stopped.so what's requirement is

if exception occurs for a record then i should skip that record and continue the process with remaining records.

is there any solution ?

Thanks in advance

[Ravi Kiran Va]

I assume you have many Statement.executeUpdate() for this purpose ,

so run every statement.executeUpdate() with in try and catch blocks .

clarify if you dont understand this .

[Sandeep Sanaboyina]

I don't think there will be any direct way to solve that issue. Your best bet is to avoid that exception by first cleaning the data or filtering it before inserting.

Since you are inserting lakhs of records, inserting one record at a time will drastically increase the time taken to complete the job. So, that solution might not work for your case.

[Martin Vashko]

If you are on Oracle 10.2 or higher, you could utilize DML error logging, which seems to suit exactly your case. See

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#sthref3393
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261