Select Statement Causing an ORA-00001?

March 11, 2010

Sometimes I receive seemingly interesting emails showing Oracle errors – leaving me to ponder… certainly, that can’t cause an error, can it?  Here is one that I received a year ago (paraphrased):

The commercially developed application that we are using displayed an error message identifying a SELECT statement as the source of an ORA-00001 error.  What is the source of the Oracle constraint error?  The error message displayed by the application is as follows:

select account_period
from PROJECT_SUMMARY
where project_id = :m_saProjSumProjectID[nProjSumIndex]
and id =:m_saProjSumSavedID[nProjSumIndex]
into :nACCOUNT_PERIOD

ORA-00001: unique constraint (TESTUSER.SYS_C006354) violated

This transaction has resulted in violating an Oracle defined constraint.
Constraints are enforced by the database manager.  This transaction has
been rolled back.

My first thought at the sight of this error was that the commercially developed application was actually displaying one of the SQL statements that was executed after the SQL statement which triggered the primary key violation.  It is easy to let a runtime error slide through for a period of time before the program notices that an error happened – maybe it is just a sign of sloppy programming (I hope not, because this has happened in some of my custom-developed programs too).

How would we start to troubleshoot this error message?  The “SYS_C” portion of the constraint name indicates that the constraint is most likely a system generated constraint name, probably intended to enforce a uniqueness requirement for a primary key column.  Exporting the data from the database using Datapump export (or the legacy exp utility) and importing the data into a new database could cause the number following “SYS_C” to change, and it is likely that constraint SYS_C006354 in my database (that is used by the same application) is very different from that of the person who posed the question to me.

Let’s see if we are able to find the answer by working the problem in a circular fashion.  For example, let’s find the name of the index that is used to enforce the primary key constraint on of one the application’s tables:

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  INDEX_NAME LIKE 'SYS%'
  AND TABLE_NAME='INVENTORY_TRANS';

INDEX_NAME
-----------
SYS_C005168

Now that we know that the index is named SYS_C005168, we could do something like this:

SELECT
  DC.OWNER,
  DC.CONSTRAINT_NAME,
  DC.CONSTRAINT_TYPE,
  DC.TABLE_NAME,
  DC.STATUS,
  SUBSTR(DCC.COLUMN_NAME,1,30) COLUMN_NAME
FROM
  DBA_CONSTRAINTS DC,
  DBA_CONS_COLUMNS DCC
WHERE
  DC.CONSTRAINT_NAME='SYS_C005168'
  AND DC.OWNER='TESTUSER'
  AND DC.OWNER=DCC.OWNER
  AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME
ORDER BY
  DCC.POSITION;

OWNER   CONSTRAINT_NAME  CONSTRAINT_TYPE  TABLE_NAME       STATUS   COLUMN_NAME
------  ---------------  ---------------  ---------------  -------  --------------
SYSADM  SYS_C005168      P                INVENTORY_TRANS  ENABLED  TRANSACTION_ID

The above output shows that the primary key constraint SYS_C005168 enforces the uniqueness of the primary key (TRANSACTION_ID) column in the table INVENTORY_TRANS.  We just demonstrated that we now know what we already mostly knew.

In the case of the person who sent the email to me, the table name was not known.  So, we could take the last of the above SQL statements and substitute SYS_C006354 in place of SYS_C005168 to find the table name and primary key column that was violated.  If the SQL statement failed to return usable information the next step might be to enable a 10046 trace at level 4 for one of the affected sessions, and try to reproduce the problem.  A 10046 trace will list the sequence of events that led up to the error message appearing in the client application.