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
