create table emp_ext (
EMPNO    NUMBER(4),
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2))
Organization external
(type oracle_loader
default directory testdir
access parameters (records delimited by newline
fields terminated by ',')
location ('emp_ext.csv'))
reject limit 1000;

SQL> select * from emp_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

[oracle@sbnf ~]$ cat EMP_EXT_14536.log


 LOG file opened at 04/26/11 16:56:08

Field Definitions for table EMP_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    EMPNO                           CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    ENAME                           CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    JOB                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    MGR                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    HIREDATE                        CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    SAL                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    COMM                            CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    DEPTNO                          CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader

Date Cache Statistics for table EMP_EXT
  Max Size:      1000
  Entries :        11
  Hits    :         0
  Misses  :         0

select * from emp_ext

create or replace directory succeeded.
create table succeeded.

Error starting at line 20 in command:
select *
from emp_ext
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EMP_EXT_3576_1028.log
 OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /vol02/opt/app/data/emp_ext.csv

CREATE OR REPLACE DIRECTORY DAT_DIR AS 'C:\example';

CREATE TABLE SAMPLE_EXT 
( 
ID NUMBER, 
BEGIN_DATE VARCHAR2(10),   
FIRST_NAME VARCHAR2(30), 
LAST_NAME VARCHAR2(30), 
STATUS VARCHAR2(10) 
)                       
 ORGANIZATION EXTERNAL
 (
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY DAT_DIR
   ACCESS PARAMETERS
   (
            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 
   )
   LOCATION ('sample.csv')
 )
 PARALLEL 4
 REJECT LIMIT 1000;

CREATE TABLE SAMPLE_TAB 
( 
ID NUMBER, 
BEGIN_DATE VARCHAR2(10),   
FIRST_NAME VARCHAR2(30), 
LAST_NAME VARCHAR2(30), 
STATUS VARCHAR2(10) 
);

CREATE OR REPLACE FUNCTION SAMPLE_ETL_FNC (P_FILE_PATH VARCHAR2 ) RETURN NUMBER AS
    l_filename varchar2(100);
    l_rowcount number;
    l_sqlerrm varchar(2000);
    l_filesize number;
   BEGIN
   
   DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'YYYY-MON-DD HH:MI:SS') || ' - BEGIN ETL PROCEDURE - Path: ' || p_file_path);
   
   --Flexible to allow for file name or file path
    if instr(p_file_path, '/') > 0 then
    select substr(p_file_path, instr(p_file_path,'/', -1)+1, 
    length(p_file_path)-instr(p_file_path,'/', -1))
    INTO l_filename
    from dual;
   else
    l_filename := p_file_path;
   end if;
   
   --Get file size if required
   --l_filesize := flength ('DAT_DIR',l_filename);
   DBMS_OUTPUT.PUT_LINE('Filename: ' || l_filename);
   
   BEGIN
    --Alter the Log and Bad file names for debugging
    --Can modify the bad and log file names if required
    DBMS_OUTPUT.PUT_LINE('ALTER EXTERNAL TABLE DATA SOURCE');
     execute immediate 'alter table SAMPLE_EXT location('''||l_filename||''')';
    --Prepare Insert SQL
    DBMS_OUTPUT.PUT_LINE('BEGIN INSERT FROM EXTERNAL TO DATA TABLE');
    execute immediate 'Insert into sample_tab select * from sample_ext A';
     l_rowcount := SQL%rowcount;
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') || ': INSERT SUCCESSFUL... IN SERTED ' || l_rowcount || ' ROWS'); 
     COMMIT;
     
     RETURN 0;
    exception      
     when others then
        DBMS_OUTPUT.PUT_LINE('INSERT FAILED');
        l_sqlerrm:=sqlerrm;
        DBMS_OUTPUT.PUT_LINE(l_sqlerrm);
       ROLLBACK; 
      commit;
       RETURN 1;
    END; 
    
   exception      
     when others then
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
       RETURN 1;
   END;

select * from sample_tab


set serveroutput on
declare
    l_return number;
    begin
    l_return := SAMPLE_ETL_FNC('july19.csv');
    dbms_output.put_line('RETURN STATUS: ' || l_return);
    end;

CREATE OR REPLACE DIRECTORY succeeded.
CREATE TABLE succeeded.
CREATE TABLE succeeded.
FUNCTION SAMPLE_ETL_FNC Compiled.

ID                     BEGIN_DATE FIRST_NAME                     LAST_NAME                      STATUS     
---------------------- ---------- ------------------------------ ------------------------------ ---------- 

0 rows selected

2011-APR-27 10:03:34 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
INSERT FAILED
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object LOG_DIR not found
RETURN STATUS: 1

KUP-04080: directory object LOG_DIR not found

  (
            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 
   )

create or replace directory BAD_DIR as 'C:\example';
create or replace directory LOG_DIR AS 'C:\example';

declare
   l_return number;
    begin
    l_return := SAMPLE_ETL_FNC('july19.csv');
    dbms_output.put_line('RETURN STATUS: ' || l_return);
   end;


create or replace directory succeeded.
create or replace directory succeeded.
2011-APR-27 10:15:49 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
27-APR-2011 10:15:54 AM: INSERT SUCCESSFUL... IN SERTED 0 ROWS
RETURN STATUS: 0

 LOG file opened at 04/27/11 10:15:49

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right


 LOG file opened at 04/27/11 10:16:11

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right

 LOG file opened at 04/27/11 10:15:53

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number


 LOG file opened at 04/27/11 10:16:11

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number

"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"

1, 19-JUL-07, John, Adams, Active

"1", "19-JUL-07", "John", "Adams", "Active"

ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS
1, 19-JUL-07, John, Adams, Active
2, 19-JUL-07, Tyler, Howell, Active
3, 19-JUL-07, Jim, Lopez, Active
4, 19-JUL-07, Carlos, White, Inactive
5, 19-JUL-07, Scott, Tiger, Active

"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"

set serveroutput on;    
declare l_return number; 
begin l_return := SAMPLE_ETL_FNC('testt.txt'); 
dbms_output.put_line('RETURN STATUS: ' || l_return); 
end;

2016-DEZ-02 06:57:01 - BEGIN ETL PROCEDURE - Path
: testt.txt
Filename: testt.txt
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
INSERT FAILED
ORA-00001: unique constraint (SYSTEM.PK_WS_T) violated
RETURN STATUS: 1

records delimited by newline 
LOAD WHEN (ID != ID)