External table cheat sheet

 /*
  *  The directory should have full 777 permission
  *  The directory in which db is trying to create log file, should have full permission
  *  The reject limit unlimited should be given so that it can reject wrong rows
  *
  */
--Creation of directory

CREATE OR REPLACE DIRECTORY raj_directory1 AS '/oracle_data';

--Creation of the external table

  DROP TABLE raj_actors;

    CREATE TABLE raj_actors (
      first_name        VARCHAR2(10),
      last_name         VARCHAR2(10),
      gender            VARCHAR2(10),
      dob               VARCHAR2(12),
      unq_num           VARCHAR2(10)
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY raj_directory1
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY '|'
        lrtrim
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL fields
        (
          first_name      CHAR(10),
          last_name       CHAR(10),
          gender          CHAR(10) ,
          dob             CHAR(12),
          unq_num         CHAR(10)
        )
      )
      LOCATION ('datafile.txt')
    )
   reject limit UNLIMITED;
----------------------------------------------------------------Table created---


SELECT * FROM raj_actors;

CREATE TABLE raj_actors_int AS SELECT * FROM raj_actors;

SELECT * FROM raj_actors_int;
TRUNCATE TABLE raj_actors_int;

describe table raj_actors_int;




--ZZZ_
--ZTBL_
--HI_