I need to join multiple files into a single one to read it from my external table Since these files are named 'randomly' (anyway I can find a pattern) for example:
ADG_LOZX723536.txt
ADG_LOZX142596.txt
I have a pkg that executes operations reading the external table and this would be a scheduled job.
How can I join it via PL/SQL having these files named differently so that I can execute my PKG and other things?
I thought to create a single job which calls a bash script This script just joins the files, (grep them by regex) into a single one which would be read by the external table. Then run the pkg via sqlplus
Anyway there is another way to just use PL/SQL?
Thank you
In Oracle 11g they added preprocessing support for external tables. This allows us to associate an OS (shell) script with the table; this script is run whenever we execute a query on the table. Find out more.
In your situation you could write a shell script to identify and munge the various files into a single source file for the table. This would be neater than having a job to call the shell script.
The PL/SQL purist in me would like to recommend a wholly in-database approach but let's be honest, shell scripts are way better suited to OS file manipulation than stored procedures.