Collections:
Define External Table in a Text File in Oracle
How To Define an External Table in a Text File in Oracle?
✍: FYIcenter.com
 
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
⇒ Run Queries on External Tables in Oracle
⇐ What Is a Directory Object in Oracle
2016-10-19, 3378🔥, 1💬
Popular Posts:
How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the T...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
How To Connect to a MySQL Server with a Port Number in MySQL? If you want to connect a MySQL server ...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...