Oracle EXTERNAL TABLE

Oracle EXTERNAL TABLE is a SQL statement in the Oracle Specific category. Oracle external tables allow querying flat files (CSV, etc.) as if they were database tables. The syntax is CREATE TABLE t (columns) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY dir LOCATION ('file.csv'));. It returns external table data. A typical example: CREATE DIRECTORY data_dir AS '/opt/oracle/data'; CREATE TABLE ext_employees ( id NUMBER(10), name VARCHAR2(100), salary NUMBER(10,2) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ('employees.csv') ) REJECT LIMIT UNLIMITED; -- Query CSV as table: SELECT * FROM ext_employees WHERE salary > 50000; -- Unlike SQL Server BULK INSERT, external tables stay linked… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).