oracle-databasebashfileunixutl-file

pl/sql join two files and read them


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


Solution

  • 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.