ORA-947 Error Thrown for Enable Table Constraint when using Exceptions Into Clause (Doc ID 2126643.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

A table was created to hold exceptions that would be generated when enabling constraints on existing tables:

CREATE TABLE "OWNER"."EXCEPTIONS_TABLE_NAME"
( "ROW_ID" ROWID,
"OWNER" VARCHAR2(30 CHAR),
"TABLE_NAME" VARCHAR2(30 CHAR),
"CONSTRAINT" VARCHAR2(30 CHAR)
);

When trying to enable constraints for any of the existing tables by using:

ALTER TABLE <any table> ENABLE CONSTRAINT <constraint_name> EXCEPTIONS INTO <owner.exceptions_table_name>

ORA-947 error was thrown:
SQL Error: ORA-00604: error occurred at level 1 recursive SQL
ORA-00947: not enough values

 

Where as , a simple enable constraint would succeed.
ALTER TABLE <any table> ENABLE CONSTRAINT <constraint_name>

Changes

None

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