Oracle External Table PREPROCESSOR: grep and find

This is based on one thread in OTN We will be using Oracle external table preprocessor feature to find file with some keyword using grep and also try to find some files with particular name using find command.
You can see my previous posts of listing files and disk usages using external table in this blog.

    [oracle@localhost ~]$ sqlplus / as sysdba  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 18 13:08:53 2016  
      
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
      
      
    Connected to:  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
      
    SQL> --My database version.  
    SQL> ----------------------  
    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> GRANT read, write, execute on DIRECTORY saubhik TO scott;  
      
    Grant succeeded.  

    [oracle@localhost saubhik]$ cat search_files.sh  
    #!/bin/bash  
    # This shell script is call by External Table's PREPROCESSOR claus.  
    # As per the external table definitin the file named as "searchfile.txt" is passed as argument 1 ($1) to this script.  
      
      
    # Reading the output of the file into a varriable  
    a=`/bin/cat $1|/bin/cut -d " " -f1 `  
    b=`/bin/cat $1|/bin/cut -d " " -f2`  
      
      
    # Listing the files. recursive search, change according to your requirement.  
    /usr/bin/find $a -type f -exec /bin/grep -H $b {} \;  
    exit  
    [oracle@localhost saubhik]$   

    SQL> ed  
    Wrote file afiedt.buf  
      
      1  create table Searchfiles_Ext  
      2         (   fname varchar2(500 BYTE)  
      3         )  
      4        organization external  
      5         ( type ORACLE_LOADER  
      6           default directory SAUBHIK  
      7           access parameters  
      8           ( records delimited by newline  
      9             PREPROCESSOR SAUBHIK: 'search_files.sh'  
    10             skip 2  
    11             badfile SAUBHIK:'searchfiles_ext%a_%p.bad'  
    12             logfile SAUBHIK:'searchfiles_ext%a_%p.log'  
      
      
      
      
    13             fields terminated by ',' lrtrim  
    14             missing field values are null (fname )  
    15                                           )  
    16           LOCATION  
    17            ( SAUBHIK:'searchfiles.txt'  
    18            )  
    19         )  
    20        reject limit unlimited  
    21*     parallel 2  
    SQL> /  
      
    Table created.  

SQL> ed  
Wrote file afiedt.buf  
  
  1  create or replace type SearchFilesType as object  
  2         (   FileName varchar2(500)  
  3*        )  
  4  /  
  
Type created.  
  
SQL> create or replace type SearchFilesTable as table of  SearchFilesType;  
  2  /  
  
Type created. 

SQL> ed  
Wrote file afiedt.buf  
  
  1      create or replace  
  2      function GetFileList  
  3        (  
  4          SearchPath varchar2,  
  5          SearchString varchar2  
  6          )  
  7        return SearchFilesTable pipelined  
  8      as  
  9        v_file_handle utl_file.file_type;  
10        v_dir_name     varchar2(50):='SAUBHIK';  
11       v_max_linesize integer     :=32767;  
12       v_file_name    varchar2(50):='searchfiles.txt';  
13       v_write_buffer varchar2(4000);  
14     begin  
15       v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize);  
16       v_write_buffer:=SearchPath||' '||SearchString;  
17       utl_file.put_line(v_file_handle,v_write_buffer,TRUE);  
18       utl_file.fclose(v_file_handle);  
19       for i in  
20       (select  
21         fname  
22       from searchfiles_ext  
23       )  
24       loop  
25         pipe row (SearchFilesType(i.fname));  
26       end loop;  
27*   end;  
SQL> /  
  
Function created. 

    SQL> select * from table(GetFileList('/home/oracle/saubhik','saubhik')) where rownum<5 --------------------------------------------------------------------------------="" 13="" 7:="" afiedt.buf:select="" arch_files.sh:="" calling="" command="" cut:="" directory="" during="" encountered="" errno="" error="" etfilelist="" exec:="" execlp="" file="" filename="" from="" home="" is="" kup-04095:="" line="" no="" or="" oracle="" preprocessor="" sa="" saubhik="" se="" search_files.sh="" searchfiles.txt:="" searchfiles_ext000_7419.log:kup-03154:="" searchfiles_ext000_7419.log:kup-04095:="" sql="" such="" table="" ubhik="">   

grep on file names, this is much easier. I have only changed the shell, everything else remain unchanged.

    [oracle@localhost saubhik]$ cat search_files.sh  
    #!/bin/bash  
    # This shell script is call by External Table's PREPROCESSOR claus.  
    # As per the external table definitin the file named as "searchfile.txt" is passed as argument 1 ($1) to this script.  
      
      
    # Reading the output of the file into a varriable  
    a=`/bin/cat $1|/bin/cut -d " " -f1 `  
    b=`/bin/cat $1|/bin/cut -d " " -f2`  
      
      
    # Listing the files. recursive search, change according to your requirement.  
    /usr/bin/find $a -name "*$b*" -print   
    exit  
    [oracle@localhost saubhik]$  

    SQL> select * from table(GetFileList('/home/oracle/saubhik','ext'));   
      
    FILENAME  
    --------------------------------------------------------------------------------  
    /home/oracle/saubhik/searchfiles_ext000_19025.log  
    /home/oracle/saubhik/searchfiles_ext000_11281.log  
    /home/oracle/saubhik/searchfiles_ext000_11040.log  
    /home/oracle/saubhik/searchfiles_ext000_18919.log  
    /home/oracle/saubhik/searchfiles_ext000_17676.log  
    /home/oracle/saubhik/searchfiles_ext000_7419.log  
    /home/oracle/saubhik/searchfiles_ext000_12996.log  
    /home/oracle/saubhik/searchfile_ext000_7419.log  
    /home/oracle/saubhik/searchfiles_ext000_18077.log  
      
    9 rows selected.  
      
    SQL> select * from table(GetFileList('/home/oracle','oracle')) where rownum<5;   
      
    FILENAME  
    --------------------------------------------------------------------------------  
    /home/oracle  
    /home/oracle/.java/.userPrefs/.userRootModFile.oracle  
    /home/oracle/.java/.userPrefs/.user.lock.oracle  
    /home/oracle/.java/.userPrefs/oracle