Common XU2 Script Error: ORA-00001

Have you written a hook script associated with adding a column to a Noetix view?  A common error I have seen is the ORA-00001 error.  I received this error most recently when I was adding some columns to a view with a command like this:

SQL> INSERT INTO n_view_column_templates

  2  (view_label

  3  , query_position

  4  , column_label

  5  , table_alias

  6  , column_expression

  7  , column_position

  8  , column_type

  9  , description

 10  , group_by_flag

 11  , gen_search_by_col_flag

 12  , profile_option

 13  , product_version

 14  , created_by

 15  , creation_date

 16  , last_updated_by

 17  , last_update_date

 18  )

 19  VALUES

 20  ('GME_MES_Melt_Batch_Xfer'    --view_label

 21  ,1     --query_position  number

 22  ,'Batch_Plan_Complete_Date'     --column_label

 23  ,'MELT'     --table_alias

 24  ,'PLAN_CMPLT_DATE'     --column_expression

 25  ,24    --column_position number

 26  ,'COL'    --column_type

 27  ,'The planned complete date associated a melt batch in '||

 28  'the transfer queue.  --XXACME'     -- description

 29  ,'N'     --group_by_flag

 30  ,'N'     --gen_search_by_col_flag

 31  , NULL  --profile_option

 32  ,'*'     --product_version

 33  ,'flinstonef'     -- created_by

 34  , SYSDATE                --creation_date

 35  ,'flinstonef'     --last_updated_by

 36  , SYSDATE                --last_update_date

 37  );

ORA-00001: unique constraint (NOETIX_SYS_TEST.N_VIEW_COLUMN_TEMPLATES_U1)

Violated

With this particular error, the constraint associated with this error are identified, so I investigate by querying this constraint name against the dba_constraints view:

  1  select
  2  constraint_name,
  3  constraint_type,
  4  table_name,
  5  index_owner,
  6  index_name
  7  from
  8  dba_constraints
  9  where 1=1
 10  and owner= 'NOETIX_SYS'
 11  and constraint_name = 'N_VIEW_COLUMN_TEMPLATES_U1'
 12* and constraint_type = 'U'

NOETIX_SYS@erptst> /

CONSTRAINT_NAME                              C TABLE_NAME                                   INDEX_OWNER
---------------------------                            --------------------                                  ------------------
N_VIEW_COLUMN_TEMPLATES_U1    U N_VIEW_COLUMN_TEMPLATES  NOETIX_SYS


INDEX_NAME
--------------------------
N_VIEW_COLUMN_TEMPLATES_U1

Sure enough, I have violated an unique constraint associated with the index, N_VIEW_COLUMN_TEMPLATES_U1, which is associated with the template table, n_view_column_templates.

To figure out the columns associated with this unique constraint, I query dba_ind_columns as follows:

This informs me that there needs to be a unique combination of values for the columns: view_label, query_position, column_label, product_version and profile_option.

Upon review of this SQL command where the error was thrown, I notice that I repeated the same insertion command twice.  After removing the erroneous command, no errors are thrown.