RE: phantom records?

Stephan,

You did not describe your table (column name & order) so I am guessing:

Can you try changing your insert statement naming your columns to make sure you 
are inserting the correct values for the correct columns?

insert into GDS_HOSTS(gds_host, sabre_column, CHANNEL_TYPE, RATE_TYPE, ...)
select 'AA', 'SABRE', CHANNEL_TYPE, RATE_TYPE ......

Maybe your columns are in a different order than you expect?

Tom


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Uzzell, Stephan
Sent: Wednesday, November 10, 2010 9:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: phantom records?

Hi listers, I've got an interesting one today:

SQL> select table_name, constraint_name, constraint_type from user_constraints 
where constraint_name = 'GDS_HOSTS_PK';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
GDS_HOSTS                      GDS_HOSTS_PK                   P

SQL> select * from user_cons_columns where constraint_name = 'GDS_HOSTS_PK';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------

  POSITION
----------
ORS                            GDS_HOSTS_PK
GDS_HOSTS
GDS_HOST
         1

Hope that makes it clear - we have a PK on one column in that table.

SQL> select gds_host from gds_hosts;

GDS_HOST
--------------------
WB
FIDELIO
MF
TW
NETBOOKER
HD
DHRCOM
EXPEDIA
DCJONV
DCAP
MOBILE

11 rows selected.

Pretty clear what we have in there...

SQL> insert into GDS_HOSTS
  2  select
  3  'AA',
  4  'SABRE',
  5  CHANNEL_TYPE,
  6  RATE_TYPE,
  7  MULTI_CURRENCY_YN,
  8  SESSION_CONTROL,
<snip>
75  ;
insert into GDS_HOSTS
*
ERROR at line 1:
ORA-00001: unique constraint (ORS.GDS_HOSTS_PK) violated

But do you see 'AA' as a gds_host in there?

And just to prove it is not a SQL syntax issue - same insert, I just changed 
line 3 from 'AA' to 'AA1':

SQL> insert into GDS_HOSTS
  2  select
  3  'AA1',
  4  'SABRE',
  5  CHANNEL_TYPE,
  6  RATE_TYPE,
  7  MULTI_CURRENCY_YN,
  8  SESSION_CONTROL,
<snip>
75  ;

1 row created.

SQL> rollback;

Rollback complete.


I've dropped and recreated the index and the constraint, analyzed the table, 
but still getting the same oddness. Any suggestions?

_____________________________________________________________________________
Stephan Uzzell | MICROS Systems, Inc.

Database Administrator - OPERA Global Technical Services
7031 Columbia Gateway Dr,  Columbia, MD  21046 | * 443.285.8000x2760 | 7 
443.285.6505

Other related posts: