Wrong Results When Querying External Tables In 9i (Doc ID 338728.1)

Last updated on NOVEMBER 25, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.7 - Release: 9.2 to 9.2
Information in this document applies to any platform.

Symptoms

Query results against external table differ:

select count(*) from load_dd;                     <-- count = 2664
select * from load_dd;                            <-- count = 72

** means has a value different from 2657.

select ROW_NUM from load_dd;                      <-- 2657 rows selected.
select CLIN  from load_dd;                        <-- 2657 rows selected.
select mod  from load_dd;                         <-- 2657 rows selected.
select SN  from load_dd;                          <-- 2657 rows selected.
select BB  from load_dd;                          <-- 2657 rows selected.
select ACRN  from load_dd;                        <-- 2657 rows selected.
select INV_TYPE  from load_dd;                    <-- 2657 rows selected.
select CLIN_PN  from load_dd;                     <-- 2651 rows selected. **
select CLIN_DESCRIPTION  from load_dd;            <-- 2641 rows selected. **
select DEL_DATE  from load_dd;                    <-- 1143 rows selected. **
select CLIN_QTY  from load_dd;                    <-- 2657 rows selected.
select CLIN_UM  from load_dd;                     <-- 2657 rows selected.
select CLIN_UNIT_PRICE  from load_dd;             <-- 2657 rows selected.
select PUR_REQ  from load_dd;                     <-- 2657 rows selected.
select PR_LINE  from load_dd;                     <-- 2657 rows selected.
select LINE_ITEM  from load_dd;                   <-- 2654 rows selected. **
select SUB_LINE_ITEM  from load_dd;               <-- 2657 rows selected.
select CLIN_SUB_PART  from load_dd;               <-- 2657 rows selected.
select SUB_PART_DIC  from load_dd;                <-- 2657 rows selected.
select SUB_QTY  from load_dd;                     <-- 2055 rows selected. **
select SUB_UM  from load_dd;                      <-- 2656 rows selected. **
select ELINS  from load_dd;                       <-- 2657 rows selected.
select ELINS_DEL_DATE  from load_dd;              <-- 1631 rows selected. **
select ELIN_QTY  from load_dd;                    <-- 2644 rows selected. **
select ELIN_UM  from load_dd;                     <-- 2657 rows selected.
select ELIN_PN_PARENT  from load_dd;              <-- 2657 rows selected.
select ELIN_PN  from load_dd;                     <-- 2657 rows selected.
select ELIN_DESCRIPTION  from load_dd;            <-- 2657 rows selected.
select CON_QTY  from load_dd;                     <-- 1042 rows selected. **
select CON_UM  from load_dd;                      <-- 2651 rows selected. **
select CON_PRICE  from load_dd;                   <-- 1734 rows selected. **
select SHIP_TO  from load_dd;                     <-- 2657 rows selected.
select COMMENTS  from load_dd;                    <-- 2657 rows selected.
select MARK_FOR  from load_dd;                    <-- 2419 rows selected. **
select MARk_QTY  from load_dd;                    <-- 2657 rows selected.
select MARK_UM  from load_dd;                     <-- 2657 rows selected.
select FOB  from load_dd;                         <-- 2657 rows selected.
select FOB_COMMENT  from load_dd;                 <-- 2657 rows selected.
select INSPECTION  from load_dd;                  <-- 2657 rows selected.
select INSP_COMMENT  from load_dd;                <-- 2657 rows selected.
select ACCEPTANCE  from load_dd;                  <-- 2657 rows selected.
select ACCEP_COMMENT  from load_dd;               <-- 2657 rows selected.

select ROW_NUM, CLIN, mod, SN, BB, ACRN, INV_TYPE,
       CLIN_PN, CLIN_DESCRIPTION, DEL_DATE, CLIN_QTY,
       CLIN_UM, CLIN_UNIT_PRICE, PUR_REQ, PR_LINE,
       LINE_ITEM, SUB_LINE_ITEM, CLIN_SUB_PART,
       SUB_PART_DIC, SUB_QTY, SUB_UM,ELINS, 
       ELINS_DEL_DATE, ELIN_QTY, ELIN_UM, ELIN_PN_PARENT,
       ELIN_PN, ELIN_DESCRIPTION, CON_QTY, CON_UM,
       CON_PRICE, SHIP_TO, COMMENTS, MARK_FOR, MARk_QTY,
       MARK_UM, FOB, FOB_COMMENT, INSPECTION,
       INSP_COMMENT, ACCEPTANCE, ACCEP_COMMENT
from   load_dd;                                   <-- 72 rows selected.

select ROW_NUM, CLIN, mod, SN, BB, ACRN,
       INV_TYPE
from   load_dd;                                   <-- 2657 rows selected.
.
select ROW_NUM, CLIN, mod, SN, BB, ACRN,
       INV_TYPE, CLIN_PN
from   load_dd;                                   <-- 2651 rows selected.
.
select ROW_NUM, CLIN, mod, SN, BB, ACRN,
       INV_TYPE, CLIN_PN, CLIN_DESCRIPTION
from   load_dd;                                   <-- 2635 rows selected. **

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