Select all rows from an external table
This operation was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
This example was developed using Oracle 9.2.0.1 on Windows 2000
This example requires a directory
CREATE DIRECTORY d000 AS "pathname";
The directory does not need to be added to the utl_file_dir parameter
Create a file within the directory e.g.
SET FEEDBACK OFF SET TRIMSPOOL ON SET SERVEROUTPUT ON SPOOL t1.csv BEGIN dbms_output.enable (100000); FOR f IN 0..99 LOOP dbms_output.put_line (f||','||LPAD (TO_CHAR (f),10,'0')); END LOOP; END; / SPOOL OFF
Create a table definition
CREATE TABLE t1 ( c1 NUMBER, c2 VARCHAR2(10) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY d000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ( c1 CHAR, c2 CHAR ) ) LOCATION ('t1.csv'); );
The statement
SELECT * FROM t1;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 EXTERNAL TABLE ACCESS (FULL) OF 'T1'