Skip to Main Content
  • Questions
  • Error Logging & Monitoring in Oracle External Table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shivani.

Asked: December 02, 2015 - 8:28 pm UTC

Last updated: December 03, 2015 - 3:47 am UTC

Version: Oracle 11.2

Viewed 1000+ times

You Asked

Hello Tom,
In my project I need to use Oracle External Tables to load data from data files which contains millions of records which I am able to do. Now requirement is:
1) If during load any record got failed I have to capture details of that record in any table
2) Also need to give details of total records in load files and how many got loaded
Please advise.

and Connor said...

Well, failed records will go into a "bad" file (you define that as part of the external table declaration).

You can then define an external table on that bad file (even if its just one column varchar2(4000), and use that to count/diagnose the bad records).

Total load counts are the same as any normal table, eg

declare
rec_count int;
begin
insert into MY_TABLE select * from MY_EXTERNAL_TABLE;
rec_count := sql%rowcount;
end;

Also, check out my video on DML error logging https://www.youtube.com/watch?v=8WV15BzQIto

Hope this helps

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the solution.This is what I wanted!!!

Shivani Gupta, December 03, 2015 - 5:37 am UTC