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 JANUARY 24, 2024
Applies to:
Oracle Financial Services Profitability Management - Version 8.1.0.0.0 and laterInformation 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 |