How to get all XML file names from a directory using EXTERNAL TABLE

Question

I am trying to get all XML file names present in a directory in order to feed them to a procedure which pulls data out of those files. Could anyone help with how I can get the file name using the EXTERNAL TABLE. I am having trouble with ACCESS PARAMETERS and LOCATION file. Don't know what exactly would go there.

Thanks

CREATE TABLE S7303786.XML_FILES
    (
      FILE_NAME VARCHAR2(255 CHAR)
    )
     ORGANIZATION EXTERNAL
         (
          TYPE ORACLE_LOADER
          DEFAULT DIRECTORY AUTOACCEPT_XMLDIR
          ACCESS PARAMETERS
          (
          RECORDS DELIMITED BY NEWLINE
           PREPROCESSOR AUTOACCEPT_XMLDIR: 'list_file.sh'
          FIELDS TERMINATED BY WHITESPACE
          )
          LOCATION ('list_file.sh')
         )
REJECT LIMIT UNLIMITED;

list_files.sh just contains the directory where the files are present. sticky.txt has nothing in it

error I am getting are :

ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04004: error while reading file /home/transfer/stu/nshstrans/sticky.txt

Answer 1

Error you got might have something to do with directory, Oracle object which points to physical directory on database server's disk. It is created by a privileged user - SYS, who then grants read and/or write privileges on it to users who will use it.

If you missed to do anything of above mentioned things, your external table won't work.

So:

SQL> show user
USER is "SYS"
SQL>
SQL> create directory mydir as 'c:\temp';

Directory created.

SQL> grant read, write on directory mydir to scott;

Grant succeeded.

SQL>

Connect to Scott and create external table:

SQL> connect scott/tiger
Connected.
SQL> create table extusers
  2    (username varchar2(20),
  3     country  varchar2(20)
  4    )
  5  organization external
  6    (type oracle_loader
  7     default directory mydir          --> this is directory I created
  8     access parameters
  9       (records delimited by newline
 10        fields terminated by ';'
 11        missing field values are null
 12          (username char(20),
 13           country  char(20)
 14          )
 15       )
 16     location ('mydata.txt')          --> name of the file that contains data
 17    )                                 --  located in c:\temp, which is MYDIR
 18  reject limit unlimited              --  directory
 19  /

Table created.

SQL>

Contents of the sample text file:

SQL> $type c:\temp\mydata.txt
Littlefoot;Croatia
Michel;France
Maaher;Netherlands
SQL>

Finally, let's select from the external table:

SQL> select * from extusers;

USERNAME             COUNTRY
-------------------- --------------------
Littlefoot           Croatia
Michel               France
Maaher               Netherlands

SQL>

Works OK, doesn't it? Now, try to do what I did.

---

On a second reading,

it appears that you don't want to read file contents, but directory contents. If that's so - apparently, it is - then see whether this helps.

In order to make it work, privileged user has to grant additional privilege - EXECUTE - to the directory.

SQL> show user
USER is "SYS"
SQL> grant execute on directory mydir to scott;

Grant succeeded.

Next step is to create an operating system executable (on MS Windows I use, it is a .bat script; on Unix, that would be a .sh, I think) which will list the directory. Note the first line - I have to navigate to a directory which is source for Oracle directory object. If you don't do that, it won't work. The .bat file is simple:

SQL> $type c:\temp\directory_contents.bat
cd c:\temp
dir /b *.txt
SQL>

Create external table:

SQL> create table extdir
  2    (line varchar2(50))
  3    organization external
  4      (type oracle_loader
  5       default directory mydir
  6       access parameters
  7         (records delimited by newline
  8          preprocessor mydir:'directory_contents.bat'
  9          fields terminated by "|" ldrtrim
 10         )
 11       location ('directory_contents.bat')
 12      )
 13    reject limit unlimited
 14  /

Table created.
SQL> connect scott/tiger
Connected.

Let's see what it returns:

SQL> select * From extdir;

LINE
-----------------------------------------------


c:\Temp>dir /b *.txt
a.txt
dept.txt
emp.txt
emps.txt
externalfile1.txt
lab18.txt
mydata.txt
p.txt
parfile_01.txt
sofile.txt
test.txt
test2.txt

15 rows selected.

SQL>

Well ... yes, those are my .txt files located in c:\temp directory.

---

As you use *nix, I think that problem you got is related to list_files.sh script. You didn't post its contents (which would probably help - not necessarily help me as I forgot almost everything I knew about *.nix), but - regarding Preprocessing External Tables (written by Michael McLaughlin), you might need to

prepend /usr/bin before the ls, find, and sed programs: /usr/bin/ls ...

See if it helps.