ZAHIR MOHIDEEN: External Table

Tuesday, April 21, 2020

Inline External table in Oracle

It is good to be back. Hope everyone is safe.
Let us pray for everyone whose lives have been impacted by #covid19.

Oracle has been enhancing "external table" features in each release .

Here is one of my post describing the support for wildcard characters in the file location.
http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

There were few external table enhancements in Oracle 18c.
One of them is that you do not need to create table first and then reference that external table in your query.

In Oracle 18c and later , I can define the external table definition inline and access the files without defining the external table.

Let us use the same example files in http://mfzahirdba.blogspot.com/2015/07/external-table-enhancements-in-oracle.html

Here is an example .

Sign on and create directory and grant the access to regular user ( in our case , zahir).

SQL> create directory EX_TAB_DIR as 'c:\datadumps\';

Directory created.

SQL> grant read , write , execute on directory EX_TAB_DIR to zahir;

Grant succeeded.

Sign on to the database with the user account.

SQL> show user
USER is "ZAHIR"

SQL> ho dir c:\datadumps
Volume in drive C has no label.
Volume Serial Number is E247-41EF

Directory of c:\datadumps

04/21/2020 05:09 PM
.
04/21/2020 05:09 PM
..
04/21/2020 04:52 PM 28 emp1.txt
04/21/2020 04:52 PM 25 emp2.txt
04/21/2020 04:53 PM 50 emp500.txt

SQL> ho type c:\datadumps\emp1.txt type c:\datadumps\emp2.txt type c:\datadumps\emp500.txt

c:\datadumps\emp1.txt

101,zahir,F
102,mohamed,R
The system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp2.txt

201,Hameed,A
252,Abdul,RThe system cannot find the file specified.
Error occurred while processing: type.

c:\datadumps\emp500.txt

511,Balasubramaniam,A
577,Yesudas,A
501,Janaki,A

SQL> col emp_id format a5
SQL> col emp_name format a20
SQL> col emp_type format a5
SQL> SELECT
2 emp_id,
3 emp_name,
4 emp_type
5 FROM
6 external
7 ( ( emp_id VARCHAR2(1000),
8 emp_name VARCHAR2(1000),
9 emp_type VARCHAR2(1000)
10 )
11 TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"
12 ACCESS PARAMETERS
13 ( debug = 3
14 RECORDS DELIMITED BY NEWLINE
15 CHARACTERSET we8mswin1252
16 BADFILE ex_tab_dir : 'emp_ext.bad_xt'
17 LOGFILE 'emp_ext.log_xt'
18 FIELDS TERMINATED BY "," LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
19 )
20 LOCATION ( 'emp*.txt' ) reject limit unlimited )
21 ;

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

7 rows selected.

In the program listing , the external table is defined inline between the lines 7 and 20.With this method, it is easier to load the data from flat files into the database without defining the table first.

Comments Welcome.

Monday, July 6, 2015

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