Use External Table

1. Set ORACLE_HOME environment variable



Unix & Linux

1. Check current value:
env | grep ORACLE_HOME


2. Change the ORACLE_HOME environment variable (valid for bash and ksh):
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=RMS
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib

PATH=$ORACLE_HOME/bin:$PATH
export PATH
echo $PATH
sqlplus / as sysdba

2. Use External Table

sqlplus / as sysdba

(no password)

1. SQLPLUS: connect sys/sys as sysdba

2. grant CREATE ANY DIRECTORY to pulseuser;
3. connect pulseuser
4. CREATE OR REPLACE DIRECTORY DATA_DIR AS '/home/oracle/ricky/universereport/20150304/';
CREATE OR REPLACE DIRECTORY dat_dir AS '/u01/pos_file/' ;
CREATE OR REPLACE DIRECTORY log_dir AS '/u01/pos_file/' ;
CREATE OR REPLACE DIRECTORY bad_dir AS '/u01/pos_file/' ;






You need to create an oracle directory called log_dir:


Code: [Select all] [Show/ hide]
  (
            records delimited by newline LOAD WHEN (ID != ID)
            badfile BAD_DIR:'SAMPLE_EXT%a_%p.bad'
            logfile LOG_DIR:'SAMPLE_EXT%a_%p.log'
            fields terminated by ',' optionally enclosed by '"' LRTRIM
            MISSING FIELD VALUES ARE NULL 
   )