Once you do this as insert-select, its just like any other insert-select, ie, its transactional, so it can all be done in the context of a transaction.

So one very easy way - set reject limit 0 on your external table. Then you just run your insert-select. If it works...all the rows got in. If it fails, none of the rows got in.

If you need to ALSO have the bad file etc, ie, you want to pick up ALL the bad rows in a file, you could do something like this (pseudo code):

1) you have an external table for the input data (call this ET_STAGE)
2) you have an external table for your bad file (call this ET_BAD)

Then its

declare
l_bad_rows int;
begin
insert /*+ APPEND */ into TARGET_TABLE
select * from ET_STAGE;

select count(*) into l_bad_rows from ET_BAD;

if l_bad_rows = 0 then
commit;
else
rollback;
end if;
end;

Hope this helps.

Your DDL is wrong, somewhere in the "access parameters" section.

External table definitions are "similar" to SQL Loader syntax but not identical.

I would start with the "external_table=generate_only" and then work from there

Thanks for the heads up on the "preview" - will check it out

OK, here is some sample data

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,09-DEC-82,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
ABCD,MILLER,CLERK,7782,23-JAN-82,1300,,10

which is meant to go into my table called EMP_TAB

SQL> desc emp_tab
 Name                          Null?    Type
 ----------------------------- -------- --------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

You can see that the *last* row of my file data is bad, since I have a string in the first column where an EMPNO is meant to be.

If *all* I care about is either loading all or none of the data, I just need an external table, eg

SQL> CREATE TABLE ext_stage
  2  (
  3    "EMPNO" NUMBER(4),
  4    "ENAME" VARCHAR2(10),
  5    "JOB" VARCHAR2(9),
  6    "MGR" NUMBER(4),
  7    "HIREDATE" DATE,
  8    "SAL" NUMBER(7,2),
  9    "COMM" NUMBER(7,2),
 10    "DEPTNO" NUMBER(2)
 11  )
 12  ORGANIZATION external
 13  (
 14    TYPE oracle_loader
 15    DEFAULT DIRECTORY TMP
 16    ACCESS PARAMETERS
 17    (
 18      RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
 19      BADFILE 'TMP':'emp_tab.bad'
 20      LOGFILE 'TMP':'emp_tab.log'
 21      READSIZE 1048576
 22      FIELDS TERMINATED BY "," LRTRIM
 23      REJECT ROWS WITH ALL NULL FIELDS
 24      (
 25        "EMPNO" CHAR(255),
 26        "ENAME" CHAR(255),
 27        "JOB" CHAR(255),
 28        "MGR" CHAR(255),
 29        "HIREDATE" CHAR(255) DATE_FORMAT DATE MASK "DD-MON-RR",
 30        "SAL" CHAR(255),
 31        "COMM" CHAR(255),
 32        "DEPTNO" CHAR(255)
 33      )
 34    )
 35    location   (  'emp_tab.dat'
 36    )
 37  )
 38  /

Table created.

SQL> insert into emp_tab
  2  select * from ext_stage;
insert into emp_tab
            *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

SQL> select * from emp_tab;

no rows selected

Because the insert failed, NO rows were loaded.

Now then *if* you ALSO need get a list of ALL of the bad rows, then you need to do more work, because in the case above, the bad file will contain only the first record rejected. That is where you would have an external table on the bad file as well, so you could query that, and EXT_STAGE would have just "REJECT LIMIT UNLIMITED" added to the definition, so that all rows in the file are processed.

I'm not sure I see a need for a temporary table.