Rem drv: <migrate type="schema_upgrade" version="24.1.0.1" />
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/emregistry/24.1.0.v/24.1.0.1/em_ne_synonyms_cutover_schema_upgrade.sql rbali_oneoff_updated/1 2025/03/14 06:41:15 rbali Exp $
Rem
Rem em_ne_synonyms_cutover_schema_upgrade.sql
Rem
Rem Copyright (c) 2025, Oracle and/or its affiliates.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/emregistry/24.1.0.v/24.1.0.1/em_ne_synonyms_cutover_schema_upgrade.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/emregistry/24.1.0.v/24.1.0.1/em_ne_synonyms_cutover_schema_upgrade.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      em_ne_synonyms_cutover_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/emregistry/24.1.0.v/24.1.0.1/em_ne_synonyms_cutover_schema_upgrade.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE:
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    akrasto     03/10/25 - synonym cutover storage table
Rem    akrasto     03/10/25 - Created
Rem


DECLARE
l_em_necutover_synonyms VARCHAR2(512) := q'[ 
CREATE TABLE EM_NECUTOVER_SYNONYMS_E (
    OWNER VARCHAR2(1024) NOT NULL,
    SYNONYM_NAME VARCHAR2(128) NOT NULL,
    TABLE_OWNER VARCHAR2(128) NOT NULL,
    TABLE_NAME VARCHAR2(128) NOT NULL,
    EDITION_NAME VARCHAR2(128) NOT NULL,
    CONSTRAINT EM_NECUTOVER_SYNONYMS_PK PRIMARY KEY (OWNER, SYNONYM_NAME, EDITION_NAME) USING INDEX COMPRESS ADVANCED HIGH
)ROW STORE COMPRESS ADVANCED 
]';
l_table_exists NUMBER;
BEGIN 
  select count(1) into l_table_exists 
  from dba_tables
  where table_name = 'EM_NECUTOVER_SYNONYMS_E'
  and owner = '&&EM_REPOS_USER';

  if (l_table_exists = 0) 
  then 
     execute immediate l_em_necutover_synonyms;
     execute immediate 'COMMENT ON TABLE EM_NECUTOVER_SYNONYMS_E IS ''Contains Synonym Data for NE synonyms cutover.''';
     execute immediate 'COMMENT ON COLUMN EM_NECUTOVER_SYNONYMS_E.OWNER is ''The OWNER for synonym to be cutover.''';
     execute immediate 'COMMENT ON COLUMN EM_NECUTOVER_SYNONYMS_E.SYNONYM_NAME is ''The SYNONYM_NAME for synonym to be cutover.''';
     execute immediate 'COMMENT ON COLUMN EM_NECUTOVER_SYNONYMS_E.TABLE_OWNER is ''The TABLE_OWNER for synonym to be cutover.''';
     execute immediate 'COMMENT ON COLUMN EM_NECUTOVER_SYNONYMS_E.TABLE_NAME is ''The TABLE_NAME for synonym to be cutover.''';
     execute immediate 'COMMENT ON COLUMN EM_NECUTOVER_SYNONYMS_E.EDITION_NAME is ''The EDITION_NAME for synonym to be cutover.''';
  end if;
END;
/

show errors
 

