Overview

An External table is a read-only table that provides a method of accessing data files stored outside the database. The actual data exists outside the database, but the metadata for the External table is a kind of virtual table that exists inside the database.

The characteristics of External tables are as follows.

• Read-only table

• DML operations are not possible

• Index creation is not possible

Below is an explanation of the advantages and disadvantages of External tables.

Method

Fixed Record Format

Example of Creating an External Table with Fixed Record Format

1. Create the data file and directory.

mkdir /home/tibero/samdata

2. Create the DEPT data file. (DEPT.dat)

100 Administration   2000 2700
200 Marketing        2010 2800
300 Purchasing       1140 2700
400 Human Resources  2030 3400
500 Shipping         1210 2500
600 IT               1030 2400
700 Public Relations 2040 3700
800 Sales            1450 3500

3. Connect to tbsql.

tbsql sys/tibero

4. Create the directory object. (Requires CREATE ANY DIRECTORY privilege)

SQL> CREATE DIRECTORY SAMDATA AS '/home/tibero/samdata';

Directory 'SAMDATA' created.

5. Grant privileges.

SQL> GRANT READ,WRITE ON DIRECTORY SAMDATA TO TIBERO;

Granted.

6. Connect to the user account.

SQL> conn tibero/tmax

Connected to Tibero.

7. Create the External table.

SQL> CREATE TABLE DEPT_EXT_TBL
     (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     ORGANIZATION EXTERNAL -- Specify creation of External table
     (
       DEFAULT DIRECTORY SAMDATA -- Specify directory object name
       ACCESS PARAMETERS
       (
         LOAD DATA INTO TABLE DEPT_EXT_TBL
         (
           DEPTNO POSITION(1:3), -- Specify position in data file
           DEPTNAME POSITION(5:21),
           MGRNO POSITION(22:25),
           LOCNO POSITION(26:29)
         )
       )
     LOCATION ('DEPT.dat') -- Data file name
     );

Table 'DEPT_EXT_TBL' created.

8. Query the data.

SQL> select * from DEPT_EXT_TBL;

    DEPTNO DEPTNAME                            MGRNO      LOCNO
---------- ------------------------------ ---------- ----------
       100 Administration                       2000        270
       200 Marketing                            2010        280
       300 Purchasing                           1140        270
       400 Human Resources                      2030        340
       500 Shipping                             1210        250
       600 IT                                   1030        240
       700 Public Relations                     2040        370
       800 Sales                                1450        350

8 rows selected.

Delimited Record Format

Example of Creating an External Table with Delimited Record Format

1. Create the data file directory.

mkdir /home/tibero/samdata

2. Create the DEPT data file. (DEPT1.dat)

"100","Administration","2000","2700"
"200","Marketing","2010","2800"
"300","Purchasing","1140","2700"
"400","Human Resources","2030","3400"
"500","Shipping","1210","2500"
"600","IT","1030","2400"
"700","Public Relations","2040","3700"
"800","Sales","1450","3500"
"3800","Payroll","2160","2700"

3. Connect to tbsql.

tbsql sys/tibero

4. Create the directory object. (Requires CREATE ANY DIRECTORY privilege)

SQL> CREATE DIRECTORY SAMDATA AS '/home/tibero/samdata';

Directory 'SAMDATA' created.

5. Grant privileges.

SQL> GRANT READ,WRITE ON DIRECTORY SAMDATA TO TIBERO;

Granted.

6. Connect to the user account.

SQL> conn tibero/tmax

Connected to Tibero.

7. Create the External table.

SQL> CREATE TABLE DEPT1_EXT_TBL
     (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     ORGANIZATION EXTERNAL -- Specify creation of External table
     (
       DEFAULT DIRECTORY SAMDATA -- Specify directory object name
       ACCESS PARAMETERS
       (
         LOAD DATA INTO TABLE DEPT1_EXT_TBL
         FIELDS TERMINATED BY ',' -- Specify field delimiter
         OPTIONALLY ENCLOSED BY '"' -- Specify character enclosing fields
         ESCAPED BY '\\' -- Specify ESCAPE character for special characters
         LINES TERMINATED BY '\n' -- Specify record terminator
         IGNORE 0 LINES -- Specify lines to ignore (if 5, lines 1~5 are ignored)
           (
             DEPTNO,
             DEPTNAME,
             MGRNO,
             LOCNO
           )
         )
     LOCATION ('DEPT1.dat')
     );
     
Table 'DEPT1_EXT_TBL' created.

8. Query the data.

SQL> select * from DEPT1_EXT_TBL;

    DEPTNO DEPTNAME                            MGRNO      LOCNO
---------- ------------------------------ ---------- ----------
       100 Administration                       2000       2700
       200 Marketing                            2010       2800
       300 Purchasing                           1140       2700
       400 Human Resources                      2030       3400
       500 Shipping                             1210       2500
       600 IT                                   1030       2400
       700 Public Relations                     2040       3700
       800 Sales                                1450       3500
      3800 Payroll                              2160       2700

9 rows selected.