GP7
asked on
After loading data from flat files into oracle tables, how to calculate number of rows loaded, inserts, updates etc
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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.
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.
Are you using the merge statement or insert & update to get data into your actual table from your external table ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ideally speaking the below should be verified after loading...
total record count = insert count + update count + err count + discard count ;
total record count = insert count + update count + err count + discard count ;
ASKER
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.
But, I appreciate you for noticing it. :)
Thank you.
ASKER
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...
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...
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