SQL query how to find !!

Thanks in advance


I am running a batch of 5000 inserts & update sql for a particular user. Out of 5000 sql some are getting executed and some are
failing

I want to know how to find those failed ones.

apart from spool option any other solution is there. let me know .

take copy of the table before update.

take copy of the table after update.

now you compare these two.

difference in count is the successful inserts.

difference in values is the successful updates.

-Raja

thas painful...

why not handle in Exceptions..?

abhaysk,
how can we handle this in exceptions?

some more clues.....


thank you!

Raja

Code:

---

Begin
  SQL_STATEMENT;
Exception
  When Others Then
    {Do What Ever U Want};
End;

---

You can trap the sql error code / msg and act accordingly...again possible through exceptions.

U can put the SQL in a script and send the output to log file, from the log file u can know which all updates failed, correct them and reexcute.

regards
anandk

thank you abhaysk!

-Raja

If i have 100 inserts to be done, and there is a error at 50 th record, then the control jumps to Exception. Right ????

Is there any way that i just skip this bad record and continue with the rest 50 records ????

For every statement have a Begin .. Exception !!

(am not sure how u can pass the control back to next statement after error with having only an exception)..

Quote:

---

Originally posted by bang_dba
If i have 100 inserts to be done, and there is a error at 50 th record, then the control jumps to Exception. Right ????

Is there any way that i just skip this bad record and continue with the rest 50 records ????

---

Just give commit after the whole operation...it's faster and better. As for the bad record, check the spool/log file and remove them from the source.

A link to an example at Tom Kyte's website, here

For the inserts, try convert the records to a text file
and use loader to insert.
See the bad file.

THis is the way We are working for inserts.

Quote:

---

Originally posted by engiri
For the inserts, try convert the records to a text file
and use loader to insert.
See the bad file.

THis is the way We are working for inserts.

---

What benefits did you see in this method? It seems a little complex.

We find the error data and try to avoid the same at the source of the record.
Thanks

It will be a stupid method ...

Well how abt capturing the ROWID and Table_Name in an ERR_TABLE ( In the exception handling part ) ??

My example is not exactly fitting, but the automated
way helped the requirement of our client.

We have to upload data from one of our client who is running Tally,
and part of the data from his oracle database.
We convert the same to text file and load the same to Oracle.

We will load the same into a temporary table and convert the successful records to the main database, error records to another table, find and troubleshoot the errors and again
load the same to the main database.

This automation works fine (Only for inserts).