Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ebr/em_ebr_util_pkgbody.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem    
Rem em_ebr_util_pkgbody.sql
Rem    
Rem Copyright (c) 2015, 2025, Oracle and/or its affiliates.
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/ebr/em_ebr_util_pkgbody.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/ebr/em_ebr_util_pkgbody.sql

Rem EMRU_FILE_POSTINSTALL:emcore/source/oracle/sysman/emdrep/sql/core/latest/ebr/em_ebr_util_pkgbody.sql
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem    
Rem    NAME
Rem      em_ebr_util_pkgbody.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/core/latest/ebr/em_ebr_util_pkgbody.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    akrasto     08/12/24 - security fix
Rem    akrasto     07/31/24 - bug-35443818
Rem    spramani    08/02/21 - sec fix
Rem    spramani    08/23/19 - perf fixes
Rem    spramani    06/01/16 - fix # 23109757
Rem    kamlesku    04/29/16 - Bug: 23109757 - Increase the retry counter from 5:3 to 10:6 
Rem    bram        03/17/16 - Bug:22892823 Fix sql Violations
Rem    kalchand    11/16/15 - Adding Configurable Retry option for failed sql
Rem                           statements
Rem    kalchand    10/21/15 - Modified - Bug: 21849469 Include Undo_EV_Creation procedure
Rem    kalchand    09/10/15 - Modified - Incorporate Robert Abbott comments, using mixed case in sql and truncate temp table handling
Rem    kalchand    09/10/15 - Bug 21501747 - SECURITY (ZSQI):
Rem                           SYSMAN.EM_EBR_UTIL Package Sql Code Violations
Rem    kalchand    08/05/15 - Modified - Fix For Bug : 21465958  Revert Delete to Truncate
Rem    cangappa    03/23/15 - Created
Rem




DEFINE EM_REPOS_USER = "SYSMAN";

create or replace package body em_ebr_util as
l_cnt2 number := 0;


FUNCTION GET_NEW_NAME(l_name varchar2, l_num number) return varchar2
is
l_len number;
l_strcnt number;
l_str varchar2(64);
l_new_name varchar2(64);
--l_word varchar2(64) := 'abcdefghijklmnopqrstuvwxyzabcd';
begin



select length(l_name) into l_len from dual;
-- dbms_output.put_line('length = '||l_len);

if( l_len > 28 ) then -- characters range < 30
l_str := l_num||'_E';
l_strcnt := length(l_str);
l_new_name := substr(l_name,1,30-l_strcnt)||l_str;
else
l_new_name := l_name||'_E';
end if;
return l_new_name;
exception when others then
return '';
end get_new_name;


FUNCTION generate_mapping_for_table( p_table_list varchar2 ) return varchar2
is PRAGMA AUTONOMOUS_TRANSACTION;
l_cnt number;
l_list_cnt number;
l_count number;
l_num number;
l_counter number;
l_table_list varchar2(32000);
l_table_name varchar2(30);
l_tmp_table_name varchar2(30);
l_check number;
l_check_db number;
l_new_name varchar2(30);
l_new_list varchar2(32000);
begin

l_table_list := p_table_list;
l_new_list := null;

--select count(1) into l_cnt from all_objects where object_name='EM_EBR_EV_TABLE_MAPPING' and owner='SYSMAN';
-- if l_cnt = 0 then
-- execute immediate 'CREATE TABLE "&EM_REPOS_USER".EM_EBR_EV_TABLE_MAPPING (OLD_TABLE_NAME VARCHAR2(50),NEW_TABLE_NAME VARCHAR2(50),TABLE_OWNER VARCHAR2(50), CONSTRAINT EM_EBR_EV_TABLE_MAPPING_PK PRIMARY KEY (OLD_TABLE_NAME))';
--end if;

l_list_cnt := length(l_table_list);

while l_list_cnt > 0 loop

l_num := instr(l_table_list,',',1,1);

if l_num > 0 then
l_table_name := substr(l_table_list,1,l_num-1);
l_table_list := substr(l_table_list,l_num+1);
else
l_table_name := substr(l_table_list,l_num+1);
l_table_list := '';
end if;

l_list_cnt := length(l_table_list);

--l_check := 0;
--execute immediate 'select count(1) from "&EM_REPOS_USER".EM_EBR_EV_TABLE_MAPPING where upper(old_table_name)=:1' into l_check using upper(l_table_name);
--if l_check > 0 then
-- dbms_output.put_line('This table : '||l_table_name||' already exists');
begin
    execute immediate 'select new_table_name /*+ index(e) */ from "&EM_REPOS_USER".EM_EBR_EV_TABLE_MAPPING e where upper(e.old_table_name)=:1' into l_new_name using upper(l_table_name);

    if l_new_list is NULL then l_new_list:=l_new_name;
    else l_new_list := l_new_list||','||l_new_name;
    end if;
    continue;

exception 
    when no_data_found then 
        dbms_output.put_line('This table : '||l_table_name||' does not exist');
end;

--l_check_db := 0;
--execute immediate 'select count(1) from dba_editioning_views where owner=''SYSMAN'' and upper(view_name)=:1' into l_check_db using upper(l_table_name);
--if l_check_db > 0 then
-- dbms_output.put_line('This table : '||l_table_name||' already exists');
begin
    execute immediate 'select table_name from dba_editioning_views where owner=''SYSMAN'' and upper(view_name)=:1' into l_new_name using upper(l_table_name);
    l_new_list := l_new_list||','||l_new_name;
    if l_new_list is NULL then l_new_list:=l_new_name;
    else l_new_list := l_new_list||','||l_new_name;
    end if;
    execute immediate q'[Insert into SYSMAN.EM_EBR_EV_TABLE_MAPPING VALUES (:1, :2, 'SYSMAN')]' using upper(l_table_name),upper(l_new_name);
    Commit;
    continue;
exception 
    when no_data_found then
        dbms_output.put_line('This table : '||l_table_name||' does not  exist');
end;



l_counter := 1;
if length(l_table_name) > 28 then
l_tmp_table_name := substr(l_table_name,1,28)||'_E';
else
l_tmp_table_name := GET_NEW_NAME(l_table_name, l_counter);
end if;


while true loop

execute immediate q'[select count(1) from ((select 1 from all_tables where owner='SYSMAN' and upper(table_name) = :1) UNION (select /*+ index(e) */ 1 from SYSMAN.EM_EBR_EV_TABLE_MAPPING e where upper(e.NEW_TABLE_NAME) = :2))]' into l_count using upper(l_tmp_table_name),upper(l_tmp_table_name);

IF l_count > 0 THEN
l_tmp_table_name := GET_NEW_NAME(l_table_name, l_counter);
l_counter := l_counter + 1;
ELSE
execute immediate q'[Insert into SYSMAN.EM_EBR_EV_TABLE_MAPPING VALUES (:1, :2, 'SYSMAN')]' using upper(l_table_name),upper(l_tmp_table_name);
Commit;
EXIT;
END IF;
end loop;

-- new names list prepared
execute immediate 'select /*+ index(e) */ new_table_name from "&EM_REPOS_USER".EM_EBR_EV_TABLE_MAPPING e where upper(e.old_table_name)=:1' into l_new_name using upper(l_table_name);

if l_new_list is NULL then l_new_list:=l_new_name;
else l_new_list := l_new_list||','||l_new_name;
end if;

end loop;
return l_new_list;
exception when others then
rollback;
dbms_output.put_line('ERROR: '||SQLERRM);
end;


procedure create_editioning_view(tab_name varchar2,ev_name varchar2) as 

begin
    declare
       tabname varchar2(32);
       evname varchar2(32); 
    begin
        tabname := sys.dbms_assert.SIMPLE_SQL_NAME(tab_name);
        evname := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(ev_name); 
        --dbms_output.put_line('create or replace editioning view '||ev_name||' as select * from '||tab_name);
        execute immediate 'create or replace editioning view '|| evname || ' as select * from '|| tabname;
    end; 
end create_editioning_view;

Function legal_sql_identifier(text_of_identifier in varchar2) return varchar2
  deterministic
is
  invalid_sql_name exception;
  pragma Exception_Init(invalid_sql_name, -44003);
begin
  declare
    s constant varchar2(32) not null := sys.dbms_assert.simple_sql_name(text_of_identifier);
  begin
    return s;
  end;
exception when invalid_sql_name then
  Raise_Application_Error(-20000, 'Illegal SQL identifier: '||text_of_identifier);
  -- Device to supress "PLS-05005: subprogram... returns without value."
  return null;
end legal_sql_identifier;

Function legal_sql_name(text_of_identifier in varchar2) return varchar2
  deterministic
is
  legal_identifier constant varchar2(32) not null := legal_sql_identifier(text_of_identifier);
  opening_quote constant boolean not null :=  Instr(legal_identifier, '"', 1, 1) = 1;
  closing_quote constant boolean not null := Instr(legal_identifier, '"', Length(legal_identifier), 1) = Length(legal_identifier);
  quoted constant boolean not null := opening_quote and closing_quote;

  text_of_name constant varchar2(30) not null :=
    case quoted
      when true then Substr(legal_identifier, 2, (Length(legal_identifier) - 2))
      else Upper(legal_identifier)
    end;
begin
  return text_of_name;
end legal_sql_name;

Function table_id_from_ev_id(owner_id in varchar2, ev_id in varchar2) return varchar2
is
  owner_name constant Sys.All_Editioning_Views.Owner %type not null := legal_sql_name(owner_id);
  ev_name constant Sys.All_Editioning_Views.View_Name %type not null := legal_sql_name(ev_id);
  valid_obj_name Sys.All_Editioning_Views.View_Name %type not null := '"';
  t Sys.All_Editioning_Views.Table_Name %type not null := '"';
  is_temp_table char(1) := 'N';
begin
    valid_obj_name := sys.dbms_assert.sql_object_name(owner_name || '.' || ev_name);
    select a.Table_Name
    into table_id_from_ev_id.t
    from Sys.All_Editioning_Views a
    where a.owner = table_id_from_ev_id.owner_name
    and a.view_name = table_id_from_ev_id.ev_name;
    return legal_sql_identifier('"'||t||'"');
exception when No_Data_Found then
    select a.TEMPORARY, a.table_name into is_temp_table, table_id_from_ev_id.t from ALL_TABLES a where a.owner = table_id_from_ev_id.owner_name and a.table_name = table_id_from_ev_id.ev_name;
    if  is_temp_table = 'Y' then
       return legal_sql_identifier('"'||t||'"');
    else
       Raise_Application_Error(-20000, 'Putative EV '||ev_name||' with Owner '||owner_id||' has no base table.');
       return null;
     -- Supress "subprogram TABLE_ID_FROM_EV_ID returns without value"
    end if;
when others then
      Raise_Application_Error(-20002, 'EV '|| ev_id ||' with Owner '||owner_id||' does not exist.' || chr(10) || SQLERRM);
      return null;    
end table_id_from_ev_id;

Procedure truncate_table_through_ev(owner_id  varchar2, ev_id in varchar2)
is
  o constant varchar2(32) not null := legal_sql_identifier(owner_id);
  stmt constant varchar2(20) not null := 'truncate table ?1.?2';
  s constant varchar2(80) not null := Replace(Replace(stmt, '?1', o), '?2', table_id_from_ev_id(owner_id, ev_id));
begin
  --DBMS_Output.Put_Line(s);
  execute immediate s;
end truncate_table_through_ev;

Procedure undo_ev_creation(ev_name in  varchar2)
is
  o                 constant varchar2(32) not null := sys_context('userenv','current_schema');
  ev                constant Sys.All_Editioning_Views.View_Name %type not null := legal_sql_name(ev_name);
  valid_obj_name    Sys.All_Editioning_Views.View_Name %type not null := '"';
  t                 Sys.All_Editioning_Views.Table_Name %type not null := '"';
  l_ev_exists       number := 0;
  l_clob            CLOB;
  type trigger_col  is TABLE OF CLOB INDEX BY BINARY_INTEGER;
  trigger_col_rec   trigger_col;
  l_count           number := 0;
  l_valid_trig_name all_triggers.trigger_name%TYPE;
  stmt_rename       constant varchar2(100) not null := 'alter table ?1.?2 rename to ?3';
  stmt_drop         constant varchar2(50)  not null := 'drop view ?1.?2';  
 s                  varchar2(100)   not null := '"';

begin
  valid_obj_name := sys.dbms_assert.SIMPLE_SQL_NAME(o || '.' || ev);
  select count(1) into l_ev_exists from SYSMAN.em_ebr_ev_table_mapping where old_table_name = ev;
  if l_ev_exists = 0 then
    t := table_id_from_ev_id(o, ev);
    execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN';

    DBMS_METADATA.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',false);
    for rec in (select trigger_name from all_triggers where owner = o and table_name = ev and base_object_type = 'VIEW')
    loop
        begin
            l_count := l_count + 1;
            l_clob := regexp_replace(dbms_metadata.get_ddl('TRIGGER', rec.trigger_name, o),'(CREATE OR REPLACE EDITIONABLE TRIGGER )("[A-Z]+"\.)(.+)(ALTER TRIGGER .+)', '\1\3', 1, 0, 'n');
            l_clob := REGEXP_REPLACE(l_clob, 'EDITIONABLE TRIGGER', 'TRIGGER', 1, 0);
            l_clob := REGEXP_REPLACE(l_clob, ';\s*\/', ';', 1, 0);
            trigger_col_rec(l_count) := l_clob;
            l_valid_trig_name := sys.dbms_assert.schema_name(o) || '.' || sys.dbms_assert.Enquote_Name(rec.trigger_name,FALSE);
            execute immediate 'drop trigger ' || l_valid_trig_name;
        exception 
              when NO_DATA_FOUND 
                  then exit;
              when others then 
                  SYSMAN.MGMT_EMDEP_LOGGER.log_message(1, 'EBR_UNDO_EV_CREATION', 'Failed to extract Triggers DDL or drop triggers on EV', 'Error : ' || SQLERRM);
              raise;
        end;
    end loop;
    
    -- drop EV and rename table to older name i.e EV name
    begin
      if t is not null then 
        s := Replace(Replace(stmt_drop, '?1', o), '?2', ev);
        execute immediate s;
        s := Replace(Replace(Replace(stmt_rename, '?1', o), '?2', t), '?3', ev);
        execute immediate s;
      end if;
      
      if trigger_col_rec.COUNT > 0 then
        for i in trigger_col_rec.FIRST .. trigger_col_rec.LAST
        loop
          begin
            execute immediate trigger_col_rec(i);
            --dbms_output.put_line(trigger_col_rec(i));
          exception
            when others then Raise_Application_Error(-20002, 'Failed to create trigger on Table '  || ev_name || chr(10) || SQLERRM);
          end;
        end loop;
      end if;
      exception 
      when others then Raise_Application_Error(-20004, 'Failed to drop or rename '  || ev_name || chr(10) || SQLERRM);
    end;
  else
    Raise_Application_Error(-20008, 'Caution : EM Fwk Table ' || ev_name || ' editioning view is not allowed to alter or drop .... ');
  end if;
end undo_ev_creation;

Procedure config_sql_retry(sql_statement in  varchar2, sql_error_msg varchar2)
is
  l_retry_exist     number := 0;
  l_retry_value     varchar2(30) := '"';
  l_retry_count     number := 0;
  l_retry_duration  number := 0;
  l_error_message   varchar2(256);
begin
  MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID', 'DDL to execute: ' || sql_statement);
--  execute immediate q'[select count(1) into l_retry_exist from "&EM_REPOS_USER".MGMT_UPGRADEUTIL_PROPS where property_name = 'RETRY_COUNT:RETRY_DURATION']';
    select count(1) into l_retry_exist from "&EM_REPOS_USER".MGMT_UPGRADEUTIL_PROPS where property_name = 'RETRY_COUNT:RETRY_DURATION';
  if l_retry_exist = 0 then
      MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID', 'DDL: '||sql_statement||' RETRY_COUNT:RETRY_DURATION=' || l_retry_exist);
      insert into "&EM_REPOS_USER".MGMT_UPGRADEUTIL_PROPS values ('RETRY_COUNT:RETRY_DURATION', '120:5');
          l_retry_count := 120;
          l_retry_duration := 5;
    commit;
  else
--     execute immediate q'[select property_value into l_retry_value from "&EM_REPOS_USER".MGMT_UPGRADEUTIL_PROPS where property_name = 'RETRY_COUNT:RETRY_DURATION']';
         select property_value into l_retry_value from "&EM_REPOS_USER".MGMT_UPGRADEUTIL_PROPS where property_name = 'RETRY_COUNT:RETRY_DURATION';
         MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID', 'DDL: '||sql_statement||' RETRY_COUNT:RETRY_DURATION=' || l_retry_exist);
         l_retry_count := substr(l_retry_value, 1, instr(l_retry_value,':',1,1)-1);
         l_retry_duration := substr(l_retry_value, instr(l_retry_value,':',1,1)+1, length(l_retry_value));
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID', 'DDL: '||sql_statement||' RETRY_COUNT: ' || l_retry_count || ' RETRY_DURATION= ' || l_retry_duration);
  end if;

  for l_retry in 1..l_retry_count loop
    MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID','executing DDL: '||sql_statement||' , retry count: ' || l_retry);
    begin
      execute immediate SYS.DBMS_ASSERT.NOOP(sql_statement);
      EXIT;
    exception when others then
       DBMS_LOCK.Sleep(l_retry_duration);
       l_error_message := 'DDL: '||sql_statement||' error: ' || SQLERRM;
       MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID',l_error_message);
       if l_retry = l_retry_count then
         MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'DDL_RETRY','DDL_RETRY_ID', 'DDL: '||sql_statement||' error: ' || sql_error_msg || ' - after retrying logic for ''' || sql_statement || ''' Error : ' || SQLERRM);
	 raise_application_error(-20000, sql_error_msg || ' - after retrying logic for ''' || sql_statement || ''' Error : ' || SQLERRM);
       else
         continue;
       end if;
    end;
  end loop;
end config_sql_retry;

begin
select count(1) into l_cnt2 from all_objects where
       object_name='EM_EBR_EV_TABLE_MAPPING' and owner='SYSMAN';
if l_cnt2 = 0 then
   execute immediate 'CREATE TABLE "SYSMAN".EM_EBR_EV_TABLE_MAPPING
   (OLD_TABLE_NAME VARCHAR2(50),NEW_TABLE_NAME VARCHAR2(50),TABLE_OWNER
    VARCHAR2(50), CONSTRAINT EM_EBR_EV_TABLE_MAPPING_PK PRIMARY KEY
    (OLD_TABLE_NAME))';
end if;
select count(1) into l_cnt2 from all_indexes where
index_name='EM_EBR_EV_TABLE_IDX' and owner='SYSMAN';
if l_cnt2 = 0 then
    execute immediate 'CREATE UNIQUE INDEX EM_EBR_EV_TABLE_IDX ON EM_EBR_EV_TABLE_MAPPING_E (UPPER(OLD_TABLE_NAME))';
end if;

end em_ebr_util;
/



