Wrong Behavior With Select From External Table (Doc ID 452014.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.1 - Release: 9.2 to 10.2
Information in this document applies to any platform.

Symptoms

The following statement:

insert INTO PERSONNEL_UPD_DEL_LOG
(
   EMP_NUM,
   CHANGE_DATE,
   CHANGE_TYPE,
   PHONE_NUM,
   MAIL_STOP,
   ABBREV_FULL_NAME,
   BLDG_NUM,
   ROOM_NUM,
   ORG_CODE,
   AFFILIATION_NAME,
   EMP_TYPE,
   EMP_CLASS,
   CONTRACT_NUM,
   EMAIL
)
SELECT EMP_NUM,
       to_date (to_char (SYSDATE - 1, 'DD-MON-YYYY'), 'DD-MON-YYYY'),
       'UPDATE',
       PHONE_NUM,
       MAIL_STOP,
       ABBREV_FULL_NAME,
       BLDG_NUM,
       ROOM_NUM,
       ORG_CODE,
       AFFILIATION_NAME,
       EMP_TYPE,
       EMP_CLASS,
       CONTRACT_NUM,
       EMAIL
FROM   PERSONNEL P
WHERE  EXISTS (SELECT '*'
               FROM   LOAD_PERSONNEL LP
               WHERE  LP.EMP_NUM = P.EMP_NUM AND
                      (NVL (LP.PHONE_NUM, '0000000000') != NVL (P.PHONE_NUM, '0000000000') OR
                       NVL (LP.MAIL_STOP, 'X') != NVL (P.MAIL_STOP, 'X') OR
                       NVL (LP.ABBREV_FULL_NAME, 'X') != NVL (P.ABBREV_FULL_NAME, 'X') OR
                       INSTR (NVL (P.BLDG_NUM, 'X'), NVL (LP.BLDG_NUM, 'X')) = 0 OR
                       NVL (LP.ROOM_NUM, 'X') != NVL (P.ROOM_NUM, 'X') OR
                       NVL (LP.ORG_CODE, 'X') != NVL (P.ORG_CODE, 'X') OR
                       (P.EMP_CLASS != 'CS' AND NVL (LP.ORG_NAME, 'X') != NVL (P.AFFILIATION_NAME, 'X')) OR
                       NVL (LP.EMP_TYPE, '0') != NVL (P.EMP_TYPE, '0') OR
                       replace (replace (replace (NVL (LP.CONTRACT_NUM, 'X'), ' ', ' '), ' ', ' '), ' ', ' ') != NVL (P.CONTRACT_NUM, 'X') OR
                       NVL (LP.EMAIL, 'X') != NVL (P.EMAIL, 'X')));

where LOAD_PERSONNEL is an external table, apparently works but a log and a bad files are written. The error in the log file is ORA-1401 in 9.2.0.8 or ORA-12899 in 10.2.0.3.

If run in serial mode, the bad file contains a record that is made up of  the beginning of a partial record of row N (where N is not equal to 1).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms