Search code examples
oracle-databaseexternal-tables

oracle external table iterating files

I'm loading files into Oracle via external table. Files will be dropped into the directory about one per minute. What is the best method to iterate through these files to load? Once loaded, I don't need connection to that file any longer but need to change to a new file.

Solution

  • preporcessor!

    You can copy your new data to the common file (override or append depending on your needs)

    ORGANIZATION EXTERNAL
(
ACCESS PARAMETERS
(
...
PREPROCESSOR bin_dir: 'copy_my_file_to_common_file.bat'
)
LOCATION ('common_file.txt')
)

    It will be called every time table is accessed