Does Your XU2/XU5 Script Produce an ORA-02291 Integrity Constraint Error?

A common error I have encountered when I regenerate my Noetix Views is the ORA-02291 integrity constraint error.  To provide a little more context regarding this entry, the example error I am looking at today is being thrown during a call to the hook script,   'OE_List_Lines_Base_xu2.sql’.

This error occurs during stage 4 of a regeneration and specifically, just after the template tables are loaded (this is when the xu2 scripts are invoked).  As a matter of observation, one can peruse the corresponding ‘.lst’ files generated by your hook scripts like this one, 'OE_List_Lines_Base_xu2.sql’, just before the second prompt for the APPS password (of course, this is using the GUI version of the NVA).  

When I am doing new development, I always perform a search in my ‘.lst’ files generated by my new hook scripts for the characters, ‘ORA-‘, to see if an error is being thrown.   One can just run your new hook scripts without running the NVA and check to see if an error will be thrown (but I would recommend removing the commits and I would recommend a rollback after the script is invoked).  Of course since this is development, we want to do this in a development environment.  Nothing is worse than testing or developing in a production environment.

Okay, so I developed my little hook script and just after I know that SQL Loader has loaded all the template tables with our configuration of the Noetix Views, I start looking at my ‘.lst’ hook scripts being generated and I spot this error in the ‘.lst’ file, 'OE_List_Lines_Base_xu2.lst':

Example:

ERROR at line 1:

ORA-02291: integrity constraint (NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES_FK5)

Violated - parent key not found

Source offending script:

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  ('OE_List_Lines_Base'    --view_label

 21  ,1     --query_position  number

 22  ,'Sales_Document_Name' --column_label

 23  ,'OBHA'     --table_alias

 24  ,'SALES_DOCUMENT_NAME'     --column_expression

 25  ,82    --column_position number

 26  ,'COL'    --column_type

 27  ,'The sales document name --XXCMFG'-- description

 28  ,'N'     --group_by_flag

 29  ,'N'     --gen_search_by_col_flag

 30  , NULL  --profile_option

 31  ,'*'     --product_version

 32  ,'flinstonef'     -- created_by

 33  , SYSDATE                --creation_date

 34  ,'flinstonef'     --last_updated_by

 35  , SYSDATE                --last_update_date

 36  );

INSERT INTO n_view_column_templates

When we look up the constraint, NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES_FK5, using the public view, dba_cons_columns with the following query, we see the following results:

select

 table_name,

 column_name

 from

 dba_cons_columns

 where

 constraint_name = 'N_VIEW_COLUMN_TEMPLATES_FK5';

___________________________________________________________

Table                                                                                  Column

___________________________________________________________

N_VIEW_COLUMN_TEMPLATES                               VIEW_LABEL

N_VIEW_COLUMN_TEMPLATES                               QUERY_POSITION

N_VIEW_COLUMN_TEMPLATES                               TABLE_ALIAS

Consequently, we know that there is foreign key constraint composed of these three columns.

This leaves me with three areas to check:

Is there a record in the N_VEIW_TEMPLATES table for this view label?  Yes.

Is there a record in the N_VEIW_QUERY_TEMPLATES table for this view label and query position one?  Yes.

Is there an insertion argument (or existing record) in the N_VEIW_TABLE_TEMPLATES table for this view label, query position and table?  Well, no.

It turns out that the insertion statement for the N_VIEW_TABLE_TEMPLATES was malformed (specifically, I have a typographical error associated with my view label).

To conclude, Noetix template tables are designed in such a way that when records are inserted, if there is a malformed command, the errors being thrown are clear enough that one can do some detective work and fix your error. 

I really appreciate how Noetix has “well instrumented” their code so that I do not need to do a lot of work to figure out where my errors are occurring and why they are occurring.

Of course, you can try to minimize your typographical errors and that will also help in your development of hook scripts.