CMS Snapshot Analysis - Run Analyzer - Fatal Error - Oracle Forms with Core CAST AIP - SQL statement select INT DBEPRO CREATE I IDJOB

This page deals with the issue when Oracle Forms Report Analysis is crashing after the saving step with the SQL error message : 

 
SQL Error: ERROR: null value in column "objtyp" violates not-null constraint 
SQL Error: DETAIL: Failing row contains (10930830, Forms_and_reports_17627 used by Forms_and_reports_17627, null, XXXXXX, -1, 0, 0, null, ???, 2016-05-01 23:13:37.831, null, 0, null). 
SQL Error: CONTEXT: SQL statement "insert into Keys(IdKey, KeyNam, KeyLib, KeyTyp, KeySubTyp, KeyClass, KeyProp, ObjTyp, IdUsrDevPro, KeyDevDat, SqlOwner) 
SQL Error: values (IO_IdKey, I_KeyNam, I_KeyLib, I_KeyTyp, I_KeySubTyp, I_KeyClass, I_KeyProp, I_ObjTyp, '???', L_KeyDevDat, I_Owner)" 
SQL Error: PL/pgSQL function i_keys(integer,character varying,character varying,character,integer,integer,integer,integer,character varying) line 15 at SQL statement 
SQL Error: SQL statement "select I_Keys (IO_IDPRO, I_PRONAM, cast(null as varchar), 'XXXXXX', -1,0,0 , I_OBJTYPPRO, NULL)" 
SQL Error: PL/pgSQL function int_subpro_create(integer,character varying,integer,integer,integer,integer) line 25 at SQL statement 
SQL Error: SQL statement "select INT_SUBPRO_CREATE(L_IDPRO_SUBSET, L_PRONAM_SUBSET, L_OBJTYP_SUBSET, I_IDJOB, L_IDPROMAIN, 8)" 
SQL Error: PL/pgSQL function int_dbepro_create(integer) line 208 at SQL statement 
SQL Error: SQL statement "select INT_DBEPRO_CREATE(I_IDJOB)" 

Below is the step-by-step scenario leading to the problem:

• Run analysis on Oracle Forms and Reports application.

The impact of the problem on the analysis or the dashboard is: Analysis crashes after the saving step.

The reason for this issue is in column "ObjTyp" of table Keys which is set to not null constraint but there is a null value assigned to this column and hence we are getting this error.

To fix the problem, proceed as follows:

As we see the error occurs at the procedure INT_DBEPRO_CREATE.In INT_DBEPRO_CREATE stored procedure. There is a below attached query for building the list of server analysis based on the category 140388 /* CAST_SQL_ProjectWithKBStorage */ in order to identify the type of the database subset.

But this category also contains forms analysis for which there is no database subset. So, when trying to create the subset project, the object type is null.

1. Run the below attached script on local base and run the analysis.

INT_DBEPRO_CREATE_without_Forms_Subset.sql

Impact of the solution on the analysis or the dashboard is: The analysis will run successfully.The script has no impact on transactions.