1

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

Mike John
  • 75
  • 2
  • 8

1 Answers1

0

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • gonna try it and let you know, ty – Mike John Mar 29 '17 at 07:51
  • KUP-04095: preprocessor command encountered error "error during exec: errno is 2", my script is 755 (oracle:oinstall), it just execute echo "test" > test.txt but I'm getting this, any idea? – Mike John Mar 29 '17 at 12:35
  • Could be anything. Does the shell script run when you execute it in the OS as the `oracle` user? What is the `$PATH` setting? – APC Mar 29 '17 at 12:39