Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Define External Table in a Text File

By: FYIcenter.com

(Continued from previous topic...)

How To Define an External Table in 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

(Continued on next topic...)

  1. What Is the Simplest Tool to Run Commands on Oracle Servers?
  2. What Is the Quickest Way to Export a Table to a Flat File?
  3. How To Export Data with a Field Delimiter?
  4. What Is SQL*Loader?
  5. What Is a SQL*Loader Control File?
  6. How To Load Data with SQL*Loader?
  7. What Is an External Table?
  8. How To Load Data through External Tables?
  9. What Are the Restrictions on External Table Columns?
  10. What Is a Directory Object?
  11. How To Define an External Table in a Text File?
  12. How To Run Queries on External Tables?
  13. How To Load Data from External Tables to Regular Tables?
  14. What Is the Data Pump Export Utility?
  15. What Is the Data Pump Import Utility?
  16. How To Invoke the Data Pump Export Utility?
  17. How To Invoke the Data Pump Import Utility?
  18. What Are Data Pump Export and Import Modes?
  19. How To Estimate Disk Space Needed for an Export Job?
  20. How To Do a Full Database Export?
  21. Where Is the Export Dump File Located?
  22. How To Export Your Own Schema?
  23. How To Export Several Tables Together?
  24. What Happens If the Imported Table Already Exists?
  25. How To Import One Table Back from a Dump File?
  26. What Are the Original Export and Import Utilities?
  27. How To Invoke Original Export/Import Utilities?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...