My Oracle Support Banner

OFSAA 8.1 Schema Creator ORA-00942: table or view does not exist Error Creating the View_der_ent_column_tpose (Doc ID 2765870.1)

Last updated on APRIL 02, 2021

Applies to:

Oracle Financial Services Profitability Management - Version 8.1.0.0.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI / AAI)
Oracle Financial Services Profitability Management (PFT)
Oracle Financial Services Funds Transfer Pricing (FTP)
Oracle Financial Services Asset Liability Management (ALM)
Oracle Financial Services Enterprise Performance Management (EPM)

Symptoms

On PFT 8.1, when executing schema creator script sysdba_output_scripts.sql an error is displayed "ORA-00942: table or view does not exist".

ERROR

SP2-0341:
line overflow during variable substitution (>3000 characters at line 1)
create or replace view view_der_ent_column_tpose as(select vdt.DSN_ID, vdt.Der_Ent_Code, vdt.Der_Ent_VERSION,vdt.metadata_guid,mem.n_element_order ELEMENT_ORDER,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'SHORTDESCRIPTION' then mem.v_element_value end) as SHORT_DESC,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'METADATATYPE' then mem.v_element_value end) as METADATA_TYPE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'METADATACODE' then mem.v_element_value end) as METADATA_CODE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'DATATYPE' then mem.v_element_value end) as DATA_TYPE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'SIZE' then mem.v_element_value end) as SIZE_,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'IDENTITYINCREMENT' then mem.v_element_value end) as IDENTITY_INCREMENT,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'ORDINAL' then mem.v_element_value end) as ORDINAL,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'LOGICALDATATYPE' then mem.v_element_value end) as LOGICAL_DATA_TYPE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'ISNULLABLE' then mem.v_element_value end) as IS_NULLABLE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'PRECISION' then mem.v_element_value end) as PRECISION_,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'SCALE ' then mem.v_element_value end) as SCALE,MAX(case when mem.v_element_parent_code = 'DERIVEDCOLUMN' AND mem.v_element_code = 'NAME' then mem.v_element_value end) as NAME_ from view_derived_entity_tpose vdt left outer join metadata_element_master mem on vdt.DSN_ID =mem.v_metadata_infodom and vdt.Der_Ent_Code =mem.v_metadata_code and vdt.Der_Ent_VERSION =mem.n_metadata_version group by vdt.DSN_ID, vdt.Der_Ent_Code, vdt.Der_Ent_VERSION, vdt.metadata_guid, mem.n_element_order)
*
ERROR at line 1:
ORA-00942: table or view does not exist



The script is attempting to create view view_der_ent_column_tpose using view_derived_entity_tpose and metadata_element_master.
After reviewing the objects it appears that object view_derived_entity_tpose does not exist.  The view is missing because the DDL string exceeds 3000 characters.
This is a base installation of OFSPM 8.1 patch V998584. This is a fresh install.

WORKAROUND
Modify the sysdba_output_scripts.sql and add a newline about midway through the DDL string.

The issue can be reproduced at will with the following steps:
1. Patch 31545589 was applied prior to executing schema creator. SchemaUtil.jar was copied into .../schema_creator/lib directory.
2. Schema creator was generated as ./osc.sh -o -s

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.