Let's create the demonstration user, and prepare its environment to create an External Table. The example that will be developed first refers to the External Table using the ORACLE_LOADER driver.
create user EXTTABDEMO identified by ORACLE default tablespace USERS; alter user exttabdemo quota unlimited on users; grant CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, ALTER SESSION, CREATE VIEW, CREATE ANY DIRECTORY to EXTTABDEMO;
A simple formatted spool from this query will generate the required external table demonstration data. The original source table is the demonstration HR.EMPLOYEES table.
select EMPLOYEE_ID ||','|| DEPARTMENT_ID ||','|| FIRST_NAME ||','|| LAST_NAME ||','|| PHONE_NUMBER ||','|| HIRE_DATE ||','|| JOB_ID ||','|| SALARY ||','|| COMMISSION_PCT ||','|| MANAGER_ID ||','|| EMAIL from HR.EMPLOYEES order by EMPLOYEE_ID
The above query will produce the following sample data:
