CODEWITHSUNDEEP
oracle-databaseoracle11gsql-insert
Anish Gupta
Anish Gupta

Reputation: 1

ERROR ORA 00001 FOR GHOST COLUMN SYS_NC00008$ IN ORACLE SQL DEVELOPER

I'm getting a unique constraint error in Oracle SQL Developer when I try to insert an entry into the table. While the column it is specifying for the unique constraint error is a ghost column -- SYS_NC00008$

( I've purposely dashed out some values while posting below query here -ignore that)

Query:

INSERT INTO "---"."---" (ID, MESSAGE_TYPE, IS_DELETED, CREATED_BY, CREATED_ON, UPDATED_BY, 
UPDATED_ON) 
VALUES ('2', '----', 'N', 'system', 
TO_TIMESTAMP('2018-08-16 07:51:25.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 
'system', TO_TIMESTAMP('2019-09-25 05:07:06.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'))

Error:

ORA-00001: unique constraint (----.----_IX0) violated ORA-06512: at line 1

Upvotes: 0

Views: 539

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

SYS_NC00008$ and columns like that are due to function based indexes being created.

SQL> create table t ( x int );

Table created.

SQL>
SQL> create index IX on t ( x + 10 );

Index created.

SQL>
SQL> select column_name
  2  from   user_tab_cols
  3  where  table_Name = 'T';

COLUMN_NAME
------------------------------
X
SYS_NC00002$

Do a query on USER_IND_EXPRESSIONS to see the contents of the index, and that will tell you where the unique violation is coming from.

SQL> select COLUMN_EXPRESSION
  2  from    user_ind_expressions
  3  where  index_name = 'IX';

COLUMN_EXPRESSION
--------------------------------------
"X"+10

Upvotes: 1

Related Questions