External table Enhancements in Oracle 12c.

One of the External tables' enhancements in Oracle 12c is the ability to use wildcard characters in defining the data sources.  We can have either ? ( to denote single character substitution ) or * ( multiple character substitution ) in denoting the location of the data files . 

Here is an simple example . 

Let us create  few CSV files. 

C:\extdir>dir

 Volume in drive C has no label.

 Volume Serial Number is 8280-8864

 Directory of C:\extdir

07/06/2015  01:45 PM              .

07/06/2015  01:45 PM              ..

07/06/2015  01:37 PM                27 emp1.txt

07/06/2015  01:38 PM                26 emp2.txt

07/06/2015  01:44 PM                52 emp500.txt

               3 File(s)            105 bytes

               2 Dir(s)  168,809,664,512 bytes free

C:\extdir>type emp1.txt

101,zahir,F

102,mohamed,R

C:\extdir>type emp2.txt

201,Hameed,A

252,Abdul,R

C:\extdir>type emp500.txt

511,Balasubramaniam,A

577,Yesudas,A

501,Janaki,A

Let us create an external table with Single character in the wild card .

CREATE TABLE EMP_EXT

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"

    ACCESS PARAMETERS

    ( debug = 3

     RECORDS DELIMITED BY NEWLINE

     CHARACTERSET we8mswin1252

     BADFILE EX_TAB_DIR:'emp_ext.bad_xt'

     LOGFILE 'emp_ext.log_xt'

     FIELDS TERMINATED BY ","

     LDRTRIM MISSING FIELD VALUES ARE NULL

     REJECT ROWS WITH ALL NULL FIELDS

    ) LOCATION ( 'emp?.txt')

  )

REJECT LIMIT UNLIMITED ;

SQL> Select * from emp_ext ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

As we have given '?' in the wildcard , the external table created above did not take in the records from emp500.txt. 

Let us create another table with '*' in the wildcard as below. 

CREATE TABLE EMP_EXT_MULTI

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR" 

    ACCESS PARAMETERS 

    ( debug = 3 

     RECORDS DELIMITED BY NEWLINE 

     CHARACTERSET we8mswin1252 

     BADFILE EX_TAB_DIR:'emp_m_ext.bad_xt' 

     LOGFILE 'emp_m_ext.log_xt' 

     FIELDS TERMINATED BY "," 

     LDRTRIM MISSING FIELD VALUES ARE NULL 

     REJECT ROWS WITH ALL NULL FIELDS 

    ) LOCATION ( 'emp*.txt')

  ) 

REJECT LIMIT UNLIMITED ;

SQL> Select * from EMP_EXT_MULTI ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

511   Balasubramaniam           A

577   Yesudas                   A

501   Janaki                    A

As can be seen from the above example , the external table did bring in the contents of all files starting with the filenames 'emp.....txt'.

- Zahir