| 30 data files handling in Oracle [message #687979] |
Sat, 05 August 2023 16:59  |
srinivas.k2005
Messages: 404
Registered: August 2006
|
Senior Member |
|
|
Oracle 12c of now and later we may move to 19c in 5 months
Hi Experts,
I have 20 different files coming to my oracle database directory independently at same time from external parties.
I have main set of 3 files where all other files will do a lookup from these processed files. The volume of files range from 20MB to 7GB.
I have below questions:
1. How to efficiently trigger the oracle package on arrival of files to Oracle directory
<My thinking> I think DBMS_SCHEDULER validating the different file path every 1 min and calling based on the folder and file respective package.
2. I have files where i need to read each line to do certain logic before staging table and few files I dump in staging table
<My thinking> If I need to read lines I will use UTL_FILE, If i just need to dump to staging table I will use External tables
3. The main problem is what is the best way to handle the dependencies across these files. Ex: After I get the main 3 files process them and other files waiting to do lookup to get certain values.
Once all files are in place then I need to trigger a main ETL ODI run.
<My thinking> Should I create packages and call then in ODI mapping and create a workflow to keep it easy rather than DBMS_SCHEDULER as it will become complex to maintain
4. What is the best approach for these 30 different files maintain different folders for each file or maintain one folder and work based on file pattern. Anybody has done this realtime implementation for these use cases.
Please suggest the best approach on above.
Thank you
[Updated on: Sun, 06 August 2023 05:11] by Moderator Report message to a moderator |
|
|
|
| Re: 30 data files handling in Oracle [message #687983 is a reply to message #687979] |
Sun, 06 August 2023 11:28  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:1. How to efficiently trigger the oracle package on arrival of files to Oracle directory
<My thinking> I think DBMS_SCHEDULER validating the different file path every 1 min and calling based on the folder and file respective package.
You need to create a File Watcher dbms_scheduler job. There are examples all over the internet.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
