After loading data from flat files into oracle tables, how to calculate number of rows loaded, inserts, updates etc

GP7
GP7 used Ask the Experts™
on
Hi all,

I need toh load data from flat files in to oracle tables. I am using external tables (one external table/flatfile and merging them with the actual tables in my database. I need to write code which will create a table that contains data like,
name of the table
what flat file was used to perform the load
start time of load
end time of load
# of rows loaded
errors encountered
# inserts
# update
etc

Could you please help me out with any ideas?

I need to load on a daily basis and this table is to verify that all the tables have been loaded successfully without any errors.

thank you very much
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I'm guessing you mean you need this information from the database itself and not the generated log files.  Times and processed rows are all captured in the BADFILE and LOGFILE parameters.

For file name/location/??? information check out USER_EXTERNAL_LOCATIONS
There's another view that has some information:  USER_EXTERNAL_TABLES

For timing of loads you'll probably need to write that yourself.  I'm not sure where you can get the good/bad row info in the data dictionary.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Prob you need to write one OS level script, that will refresh external table and make an entry in another table with result. You can use grep utility to confirm any Ora-Error and can send the mail to concern person.
to copy file from one server to another, you can either use ftp/scp etc and for database activity you can use sqlplus like this
sqlplus -s user@ORCL << EOF
--- check record count etc....
...
EOF
GP7

Author

Commented:
hi virdi ds.... could you please explain in a little more detail? what do you mean by refresh external table? When i get errors my external table is generating bad , log and discard files. Now using these files, I need to place all the discarded records in the discard tables and error records in the error tables.

For every table there will be
1. flat file
2. external table
3. log file (will be generated by external table)
4. bad file (will be generated by external table)
5. discard file ( will be generated by external table)
6. Discard table ( I need to update this table with the records from the above discard file)
7. Error table ( I need to update this table with the records from the above error file)
8. I need to create another table LOADINFO_TABLE which records table name, flat file name,when the load was started, when it ended, number of rows loaded, number of inserts , number of updates, number of discarded rows for this table, number errors for this table.

Please let me know if you have any ideas.

Thank you for your help!

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I think what virdi_ds was saying is:  What triggers the entire process?

Something has to trigger the load of new files and should be at the OS level.

I'm not sure you can get everything with one stop shopping.

You should be able to set up a generic error external table pointing to the BADFILE flat file generated.  Thinking out loud here but just one column as a CLOB should never have errors itself.

Not knowing how your external table is set up, would the DISCARD file ever be used?  If so, you should be able to do the same sort of thing, set up a DISCARD external table. to look for discarded records.

>>I need to create another table LOADINFO_TABLE
What is the difference between: rows loaded and number of inserts/updates?

Anyway, all this should be able to be handled based on the triggering OS process and the process you have to pull from the external tables.  All the information has to be part of those two events/processes.

So I'm not sure what the issues are.  For example, something has to ensure the new files make it to the server.  It should already know the filename.  This same process should also know the start and end times of the process.
Naveen KumarProduction Manager / Application Support Manager

Commented:
Are you using the merge statement or insert & update to get data into your actual table from your external table ?
Naveen KumarProduction Manager / Application Support Manager
Commented:
we have used a merge statement in a similar scenario after selecting from external table and we can only get to know how many in total got merged ( inserted + updated ) record count.

We cannot find insert count and updated count if you are using merge. otherwise you have to individual insert/update commands to get those count.

Thanks
Naveen KumarProduction Manager / Application Support Manager
Commented:
For every table there will be
1. flat file
2. external table
3. log file (will be generated by external table)
4. bad file (will be generated by external table)
5. discard file ( will be generated by external table)
6. Discard table ( I need to update this table with the records from the above discard file)
7. Error table ( I need to update this table with the records from the above error file)
8. I need to create another table LOADINFO_TABLE which records table name, flat file name,when the load was started, when it ended, number of rows loaded, number of inserts , number of updates, number of discarded rows for this table, number errors for this table.

Until step 5, we do not need to do anything.

for step 6, you need to write unix shell scripts or perl script to update the content from discard file to oracle table.

similarly for step 7 but file / table name are different.

step 8, again unix/perl script to return the load start time, load end time, number of rows loaded etc and then the script should update these details into the oracle table.
Commented:
Thank you very much for all your comments.

I found a solution and I am posting it as somebody else with a similar  requirement might find this helpful.

My aim was to get
1. # records in flat file
2. # records in external table
3. # updates
4. # inserts
5. write errors/discarded records to respective discard table.

Here is what I did,

I have a procedure which takes count in flat file as input. (So, I have the count for the flat file. so 1. is answered)
then I get count from external table (2. solved)
then I got the count from joining oracle table and external table on pk. (If the record exists , its an update right? :) )
and ...  v_insert_count := #recs in external table - v_update_count; (so 4. solved)
for the errors I used the package dbms_errlog

BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('table_name' ,'discard_table_name' ) ;
END;
/

What the above procedure does is, it creates an error table for a table. the error table has the same columns as the actual table and has 5 extra cols which have the oracle error name, number, error description etc.
The below statement needs to be put at the end of the DML,
LOG ERRORS INTO D10_LU_YEAR ('MERGE') REJECT LIMIT UNLIMITED

eg,

Insert into t1 values (1, 'A')
LOG ERRORS INTO discard_t1 ('INSERT') REJECT LIMIT UNLIMITED;

After that i just got the count from the error table for the # of errors.

Once I got all the counts, i just inserted in to my LOAD_INFO table.

Done! :)

Thank you.
Naveen KumarProduction Manager / Application Support Manager
Commented:
Good to see that detailed approach from yourself but

 v_insert_count := #recs in external table - v_update_count; --> i would say this is not 100% correct

for an example, if my flat file has 9 records in total and 5 got updated and 2 records got errored out, then the inserted count should be

v_insert_count := #recs in external table - v_update_count - v_err_count ; --> 9 - 5 - 2 = 2 but your calculation will get 4 which is not right i believe. so kindly amend this if required.
Naveen KumarProduction Manager / Application Support Manager

Commented:
ideally speaking the below should be verified after loading...

total record count = insert count + update count + err count + discard count ;
GP7

Author

Commented:
Thank you nav_kum_v ... yes, i changed the insert count to v_insert_count := #recs in external table - v_update_count - v_err_count  after qa. I forgot to update my post (#27275262) after that.

But, I appreciate you for noticing it. :)

Thank you.

GP7

Author

Commented:
I thank every body who participated in this post... I cannot abandon this question as I found a working solution to this problem (Post ID: 27275262).

I do not know how to close this question and whome to award points and would like to add this question to your knowledge base.

Thank you...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial