Oracle DBA FAQ - Loading and Exporting Data

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

How To Define an External Table with a Text File?

You can use the CREATE TABLE statement to create external tables. But you need to use ORGANIZATION EXTERNAL clause to specify the external file location and the data access driver. The tutorial exercise below shows you how to define an external table as a text file:

>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> CREATE TABLE ext_fyi_links (
    id NUMBER(4),
    url VARCHAR2(16),
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE
  ) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY test_dir
    LOCATION ('ext_fyi_links.txt')
  );
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  FROM USER_TABLES;
TABLE_NAME            TABLESPACE_NAME          NUM_ROWS
--------------------- ---------------------- ----------
REGIONS               USERS                           4
LOCATIONS             USERS                          23
DEPARTMENTS           USERS                          27
JOBS                  USERS                          19
EMPLOYEES             USERS                         107
JOB_HISTORY           USERS                          10
FYI_LINKS             USERS                           2
EXT_FYI_LINKS
COUNTRIES                                            25

How To Run Queries on External Tables?

If you have an external table defined as a text file with the ORACLE_LOADER driver, you can add data to the text file, and query the text file through the external table. By default, data fields in the text file should be terminated by ','. The tutorial exercise below shows you how add data to the external table defined in the previous exercise:

>edit /oraclexe/test/ext_fyi_links.txt
1101,dba.fyicenter,Link #1,88,07-MAY-06
1110,dev.fyicenter,Link #2,88,07-MAY-06

>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> SELECT * FROM ext_fyi_links;
       ID URL              NOTES         COUNTS CREATED
--------- ---------------- ----------- -------- ---------
     1101 dba.fyicenter    Link #1           88 07-MAY-06
     1110 dev.fyicenter    Link #2           88 07-MAY-06

How To Load Data from External Tables to Regular Tables?

Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:

SQL> CREATE TABLE fyi_links (
    id NUMBER(4) PRIMARY KEY,
    url VARCHAR2(16) NOT NULL,
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE DEFAULT (sysdate)
  );

SQL> INSERT INTO fyi_links SELECT * FROM ext_fyi_links;
2 rows created.

SQL> SELECT * FROM fyi_links;
       ID URL              NOTES         COUNTS CREATED
--------- ---------------- ----------- -------- ---------
      101 fyicenter.com    Session 1            07-MAY-06
      110 centerfyi.com    Session 1            07-MAY-06
     1101 dba.fyicenter    Link #1           88 07-MAY-06
     1110 dev.fyicenter    Link #2           88 07-MAY-06

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...