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
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
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
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';
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.