Friday, December 2, 2011

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':

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  )
 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:

 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.

No comments:

Post a Comment