Do you need to import a file into a table? do it simple with External Table.

Very often we work with environments that cannot be linked directly (with a DB link for example) and we have to acquire data by text files.
In order to reach that goal I have seen developers write procedures with many lines of code, variables end complicating their lives with loops.
Just do it simple, with "ET".


Suppose we need to import a CSV file into a table.

The file:

PAUL,1,5000,PARIS
JOHN,2,4500,ROME
MARK,3,6000,LONDON

Define the directory where the file reside:

CREATE OR REPLACE DIRECTORY IMPORT_FILE_DIR as 'c:\temp';

Where needed

GRANT READ, WRITE ON DIRECTORY IMPORT_FILE_DIR TO USR_NAME

Create the external table:

CREATE TABLE ET_EMPLOYEES
(
  ENAME            VARCHAR2(50)    NULL,
  DEPTNO           NUMBER          NULL,
  SALARY           NUMBER(50)      NULL,
  LOC              VARCHAR2(200)   NULL,

)
  ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY IMPORT_FILE_DIR
    ACCESS PARAMETERS
      (
       RECORDS DELIMITED BY NEWLINE
       CHARACTERSET WE8MSWIN1252
       FIELDS TERMINATED BY ',' LRTRIM
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (ENAME,DEPTNO,SAL,LOC)
      )
    LOCATION (IMPORT_FILE_DIR:'EMPLOYEES.csv' )
  )
  REJECT LIMIT UNLIMITED;

Put the file in the directory and that's it! No procedures, no code, no problems...

Now enjoy your table (read only).

SELECT * FROM ET_EMPLOYEES