Listing Operating system files with Oracle External Table PREPROCESSOR feature

I have read few posts/articles about listing files using external table PREPROCESSOR feature. Most of them is based on Win* systems and with some limitations.
I have taken help from the following articles:
http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html
http://www.oracle.com/technetwork/articles/servers-storage-admin/linux-from-database-2008379.html
There are some spelling mistakes in this article and please accept my apology for that. I will try to correct those in my off time.
 

 
SQL> SHOW USER
USER is "SYS"
SQL> ---- My database version
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> -- Creating the directory object.
SQL> CREATE OR REPLACE DIRECTORY SAUBHIK AS '/home/oracle/saubhik';

Directory created.

SQL> --- Granting the required permissions.
SQL> GRANT read, write, execute on DIRECTORY saubhik TO scott;

Grant succeeded.

SQL> 

[oracle@localhost saubhik]$ ## My Operating system details
[oracle@localhost saubhik]$ uname -a
Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@localhost saubhik]$ # My list_file.sh which is used in External Table Definition
[oracle@localhost saubhik]$ cat list_file.sh
# This shell script is call by External Table's PREPROCESSOR claus.
# As per the external table definitin the file named as "listfile.txt" is passed as argument 1 ($1) to this script.

#!/bin/bash

# Reading the output of the file into a varriable
a=`/bin/cat $1`

# Changing the directory.
cd $a

# Listing the files
/bin/ls -lh|/bin/awk '{print $1,",",$2,",",$3,",",$4,",",$5,",",$6,$7,",",$8,","$9}'
exit
[oracle@localhost saubhik]$ #Current content of the listfile.txt
[oracle@localhost saubhik]$ cat listfile.txt
/u01/app/oracle/product/11.2.0/db_1
[oracle@localhost saubhik]$ 

SQL> SHOW USER
USER is "SCOTT"
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> --Dropping the table before creation!
SQL> DROP TABLE LISTFILE_EXT purge;

Table dropped.

SQL> -- Creating the external table. 
SQL> ed
Wrote file afiedt.buf

  1   CREATE TABLE LISTFILE_EXT
  2     (       fpermission VARCHAR2(500),
  3        flink VARCHAR2(2),
  4        fowner VARCHAR2(500),
  5        fgroup VARCHAR2(500),
  6        fsize VARCHAR2(500),
  7        fdate VARCHAR2(20),
  8        ftime VARCHAR2(20),
  9        FNAME VARCHAR2(500 BYTE)
 10     )
 11     ORGANIZATION EXTERNAL
 12      ( TYPE ORACLE_LOADER
 13        DEFAULT DIRECTORY SAUBHIK
 14        ACCESS PARAMETERS
 15        ( RECORDS DELIMITED BY NEWLINE
 16          PREPROCESSOR SAUBHIK: 'list_file.sh'
 17          skip 2
 18          badfile SAUBHIK:'listfile_ext%a_%p.bad'
 19          logfile SAUBHIK:'listfile_ext%a_%p.log'
 20          fields terminated by ',' lrtrim
 21          missing field values are null (fpermission,
 22                                         flink ,
 23                                         fowner ,
 24                                         fgroup ,
 25                                         fsize ,
 26                                         fdate,
 27                                         ftime ,
 28                                         FNAME )
 29                                        )
 30        LOCATION
 31         ( SAUBHIK:'listfile.txt'
 32         )
 33      )
 34     REJECT LIMIT UNLIMITED
 35*   PARALLEL 2
SQL> /

Table created.

SQL>

Now, I expect that, If I do a select from my external table, then it will in turn use list_file.sh with argument listfile.txt. So, We should have the directory listing of the path mentioned in the file listfile.txt which is /u01/app/oracle/product/11.2.0/db_1, my Oracle home directory!.

So, it is working as expected!. Now I want to make it little bit flexible. That means when user will pass some directory path, it should display the files in that directory.
For that, we need to modify the listfile.txt as per user need and also Oracle user should have read access to that path (otherwise you will get an error!).
Here is my wrapper function.

SQL> --Creating the type.
SQL> ed
Wrote file afiedt.buf

  1    CREATE OR REPLACE TYPE LISTFILE_TYP AS OBJECT
  2     (       fpermission VARCHAR2(500),
  3        flink VARCHAR2(2),
  4        fowner VARCHAR2(500),
  5        fgroup VARCHAR2(500),
  6        fsize VARCHAR2(500),
  7        fdate VARCHAR2(20),
  8        ftime VARCHAR2(20),
  9        FNAME VARCHAR2(500)
 10*    )
SQL> /

Type created.

SQL> CREATE OR REPLACE TYPE LISTFILE_TBL_TYP AS TABLE OF  LISTFILE_TYP;  
  2  /

Type created.

SQL>

SQL> ed
Wrote file afiedt.buf

  1  ---My wrapper function.
  2  CREATE OR REPLACE
  3  FUNCTION get_file_list
  4    (
  5      pi_path VARCHAR2)
  6    RETURN LISTFILE_TBL_TYP PIPELINED
  7  AS
  8    v_file_handle utl_file.file_type;
  9    v_dir_name     VARCHAR2(50):='SAUBHIK';
 10    v_max_linesize INTEGER     :=32767;
 11    v_file_name    VARCHAR2(50):='listfile.txt';
 12    v_write_buffer VARCHAR2(4000);
 13  BEGIN
 14    v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize);
 15    v_write_buffer:=pi_path;
 16    utl_file.put_line(v_file_handle,v_write_buffer,TRUE);
 17    utl_file.fclose(v_file_handle);
 18    FOR i IN
 19    (SELECT fpermission,
 20      flink ,
 21      fowner ,
 22      fgroup ,
 23      fsize ,
 24      fdate,
 25      ftime ,
 26      FNAME
 27    FROM listfile_ext
 28    )
 29    LOOP
 30      PIPE ROW (LISTFILE_TYP(i.fpermission,i.flink,i.fowner,i.fgroup,i.fsize,i.fdate,i.ftime,i.fname));
 31    END LOOP;
 32* END;
SQL> /

Function created.

SQL>

Now, it's time to post some test cases!.