rem
rem NAME : ck_util
rem
rem DESCRIPTION
rem
rem   Some generic procedures used by the utilities.
rem
rem NOTES
rem
rem   They are placed in their own package because I didn't want to interfere
rem   to much with the old code.
rem
rem MODIFIED   (MM/DD/YY)
rem   TNS       14/04/99 - Bug 871081
rem                        Removed function 'locked_set_in_app'  as not required
rem                        for Oracle Repository 6.5
rem                        Modified columns on SDW_USERS ('USR' prefix removed)
rem
rem   Aloevold  11/04/96 - Creation. 
rem   ALoevold  12/11/96 - Added do_commit procedure.
rem   ALoevold  02/14/97 - Reset optimizer mode from API.
rem   ALoevold  02/21/97 - Added function to validate locked user defined sets
rem                        in application systems. 431306/431305
rem   ALoevold  04/16/97 - Fixed bug 478308. 
rem   LOldham   12/12/97 - Added application locking
rem   LOldham   12/01/98 - Added release on commit to application locking
rem

create or replace package body ck_util as

  cda_error              constant varchar2(3) := 'CDA'; 
  lock_failed_err        constant number      := 9;
  timeout_failed_err     constant number      := 10;
  app_lock_failed_err    constant number      := 11;
  app_timeout_failed_err constant number      := 12;
  app_unlock_failed_err  constant number      := 13;
  unhdl_orcl_err         constant number      := 2105;
  app_not_exist          constant number      := 6055;
  no_repos_owner         constant number      := 6098;
  timeout            constant integer     := 30;    -- Lock Timout in seconds.

/*---------------------------- Do_Commit -----------------------------------*/
/*
  Name 
    Do_Commit 
  Description
    Do commit and set use of specific rollback segment.
*/
procedure do_commit(rollback_segment in varchar2 default null) is
  proc    varchar2(100) := 'Ck_Util.Do_Commit ';
begin
  rmdbg.trace(proc || 'loc 1');
  commit;
  if ( rollback_segment is not null ) then
    rmdbg.trace(proc || 'loc 2');
    dbms_transaction.use_rollback_segment(rollback_segment);
  end if;
  rmdbg.trace(proc || 'loc 3');
end do_commit;


/*-------------------------------- Init -----------------------------------*/
/*
  Name 
    Init
  Description
    Init
*/
procedure init (called_by   in varchar2,
                called_from in varchar2 default 'Start') is
  cursor_pointer integer;
  ignore         integer;
begin

  cursor_pointer := dbms_sql.open_cursor;

  if (called_from = 'START' or called_from = 'Start') then
    dbms_sql.parse(cursor_pointer,
                  'alter session set optimizer_mode = all_rows',dbms_sql.v7); -- B3196475 : optimizer_goal -> optimizer_mode
    ignore := dbms_sql.execute(cursor_pointer);  
    rmdbg.trace('Ck_Util.Init. Optimizer goal is all rows');
  else
    if ((cdapi.optimizer_goal is not null) and
        (upper(cdapi.optimizer_goal) = 'ALL_ROWS' or
         upper(cdapi.optimizer_goal) = 'RULE' or
         upper(cdapi.optimizer_goal) = 'CHOOSE')) then
      dbms_sql.parse(cursor_pointer,
        'alter session set optimizer_mode = ' || cdapi.optimizer_goal,dbms_sql.v7); -- B3196475 : optimizer_goal -> optimizer_mode
      ignore := dbms_sql.execute(cursor_pointer);  
      rmdbg.trace('Ck_Util.Init. Optimizer mode is ' || cdapi.optimizer_goal);
    else
      dbms_sql.parse(cursor_pointer,
                 'alter session set optimizer_mode = first_rows',dbms_sql.v7); -- B3196475 : optimizer_goal -> optimizer_mode
      ignore := dbms_sql.execute(cursor_pointer);  
      rmdbg.trace('Ck_Util.Init. Optimizer goal is first rows');
    end if;
  end if;

  dbms_sql.close_cursor(cursor_pointer);

end init;


/*----------------------------- Progress -----------------------------------*/
/*
  Name 
    Progress 
*/
procedure progress as
begin
  null;
end progress;


/*---------------------------- Register ------------------------------------*/
/*
  Name 
    Register
  Description
    Register information about which utility and statement is running. 
    Information can be queried in v$session.    
*/
procedure register (name in varchar2,text in varchar2) is
begin
  if (text = 'Finish') then
    dbms_application_info.set_client_info(' ');
    dbms_application_info.set_module(' ',' ');
  else
    dbms_application_info.set_client_info(name||'. '||text);
    dbms_application_info.set_module(name,text);
  end if;
end register;

/*------------------------- Allocate Lock  ------------------------------*/
/*
  Name 
    Allocate Lock
  Description
    Allocates a unique lockhandle
  Returns
    The lock handle if successful otherwise raises a user defined exception
*/
function allocate_lock(r_owner in varchar2, name in varchar2, version in number) return varchar2 is

  lockhandle    varchar2(130);
  proc          varchar2(100) := 'Ck_Util.Allocate_Lock. ';

begin
  
  dbms_lock.allocate_unique ('D2K$_'||r_owner||'.'||name||'_'||to_char(version), lockhandle);
  rmdbg.trace (proc || name ||':'|| lockhandle);
  return lockhandle;

end allocate_lock;

/*------------------------- Allocate Lock  ------------------------------*/
/*
  Name 
    Allocate Lock
  Description
    Allocates a unique lockhandle
  Returns
    The lock handle if successful otherwise raises a user defined exception
*/
function allocate_lock(name in varchar2, version in number) return varchar2 is

  lockhandle    varchar2(130);
  proc          varchar2(100) := 'Ck_Util.Allocate_Lock. ';
  r_owner 	    varchar2(30);

  cursor     rowner is 
    select   usr.username
    from     sdw_users usr
    where    usr.sdd_owner = 'Y';
                 
begin
  
  open rowner;
  fetch rowner into r_owner;
  if (rowner%notfound) then
    rmmes.post(cda_error,no_repos_owner);
    close rowner;
    raise lock_failed;
  end if;
  close rowner;
  
  dbms_lock.allocate_unique ('D2K$_'||r_owner||'.'||name||'_'||to_char(version), lockhandle);
  rmdbg.trace (proc || name ||':'|| lockhandle);
  return lockhandle;

end allocate_lock;

/*------------------------- Lock Application ------------------------------*/
/*
  Name 
    Lock Application
  Description
    Take out user lock on an application system
  Returns
    The lock handle if successful otherwise raises a user defined exception
*/
function lock_application(appid in number, 
                          lock_mode in varchar2 default 'SHARE', 
                          free_lock_on_commit in boolean default false) 
return varchar2 is

  lockhandle    varchar2(130);
  return_code   integer;
  t_mode        integer;
  proc          varchar2(100) := 'Ck_Util.Lock_Application. ';
  t_app_name    varchar2(14);
  t_app_version number;

  cursor     app is 
    select   app.name
    ,        1    -- Changed from app.version column removed model bug #25960
    from     ci_application_systems app
    where    app.id = appid;
                
begin
  
  open app;
  fetch app into t_app_name, t_app_version;
  if (app%notfound) then
    rmmes.post(cda_error,app_not_exist);
    close app;
    raise lock_failed;
  end if;
  close app;

  if (lock_mode in ('SHARE', 'S')) then
    t_mode := 4;   -- Share mode
  elsif (lock_mode in ('EXCLUSIVE', 'E', 'X')) then
    t_mode := 6;   -- Exclusive
  end if;
  lockhandle := allocate_lock (t_app_name, t_app_version);
  return_code := dbms_lock.request (lockhandle, t_mode, timeout, free_lock_on_commit);
  rmdbg.trace (proc || 'rc ' || to_char(return_code));
  rmdbg.flush_trace;
  if (return_code = 1) then
    rmmes.post (cda_error, app_timeout_failed_err,t_app_name, to_char(t_app_version));
    raise timeout_failed;
  elsif (return_code = 4) then -- already own application lock
    if (t_mode = 6) then 
      return_code := dbms_lock.convert (lockhandle, t_mode, timeout);
      if (return_code = 1) then
        rmmes.post (cda_error, app_timeout_failed_err,t_app_name, to_char(t_app_version));
        raise timeout_failed;
      elsif (return_code = 4) then null; -- already own exclusive application lock
      elsif (return_code <> 0) then
        rmmes.post (cda_error, app_lock_failed_err,t_app_name,to_char(t_app_version));
        raise lock_failed;
      end if;
    end if;
  elsif (return_code <> 0) then
    rmmes.post (cda_error, app_lock_failed_err,t_app_name,to_char(t_app_version));
    raise lock_failed;
  end if;

  rmdbg.trace (proc || t_app_name ||':'|| lockhandle);
  return lockhandle;

end lock_application;

/*------------------------- Lock Application ------------------------------*/
/*
  Name 
    Lock Application
  Description
    Take out user lock on an application system
  Returns
    The lock handle if successful otherwise raises a user defined exception
*/
function lock_application(name in varchar2, version in number, lock_mode in varchar2 default 'SHARE',
                          free_lock_on_commit in boolean default false) return varchar2 is

  lockhandle    varchar2(130);
  return_code   integer;
  t_mode        integer;
  proc          varchar2(100) := 'Ck_Util.Lock_Application. ';

begin

  if (lock_mode in ('SHARE', 'S')) then
    t_mode := 4;   -- Share mode
  elsif (lock_mode in ('EXCLUSIVE', 'E', 'X')) then
    t_mode := 6;   -- Exclusive
  end if;
  lockhandle := allocate_lock(name, version);
  return_code := dbms_lock.request (lockhandle, t_mode, timeout, free_lock_on_commit);
  rmdbg.trace (proc || 'rc ' || to_char(return_code));
  rmdbg.flush_trace;
  if (return_code = 1) then
    rmmes.post (cda_error, app_timeout_failed_err,name,to_char(version));
    raise timeout_failed;
  elsif (return_code = 4) then -- already own application lock
    if (t_mode = 6) then 
      return_code := dbms_lock.convert (lockhandle, t_mode, timeout);
      if (return_code = 1) then
        rmmes.post (cda_error, app_timeout_failed_err,name, to_char(version));
        raise timeout_failed;
      elsif (return_code = 4) then null; -- already own exclusive application lock
      elsif (return_code <> 0) then
        rmmes.post (cda_error, app_lock_failed_err,name,to_char(version));
        raise lock_failed;
      end if;
    end if;
  elsif (return_code <> 0) then
    rmmes.post (cda_error, app_lock_failed_err,name,to_char(version));
    raise lock_failed;
  end if;

  rmdbg.trace (proc || name ||':'|| lockhandle);
  return lockhandle;

end lock_application;

/*------------------------- Lock Application ------------------------------*/
/*
  Name 
    Lock Application
  Description
    Take out user lock on an application system
  Returns
    The return code from lock request
*/
--function lock_application(lock_mode in varchar2 default 'SHARE',lockhandle in varchar2) return integer 
function lock_application(lock_mode in varchar2 default 'SHARE'
                         ,lockhandle in varchar2 default cdapi.app_sys_lock_handle
                         ,free_lock_on_commit in boolean default false) 
return integer
is

  return_code   integer;
  t_mode        integer;
  proc          varchar2(100) := 'Ck_Util.Lock_Application. ';

begin

  if (lock_mode in ('SHARE', 'S')) then
    t_mode := 4;   -- Share mode
  elsif (lock_mode in ('EXCLUSIVE', 'E', 'X')) then
    t_mode := 6;   -- Exclusive
  end if;
  return_code := dbms_lock.request (lockhandle, t_mode, timeout, free_lock_on_commit);
  if (return_code = 4) then -- already own application lock
    if (t_mode = 6) then 
      return_code := dbms_lock.convert (lockhandle, t_mode, timeout);
    end if;
  end if;
  rmdbg.trace (proc || lockhandle||' rc:'|| to_char(return_code));
  rmdbg.flush_trace;

  return return_code;

end lock_application;

/*----------------------- Release Application Lock ------------------------*/
/*
  Name 
    Release Application Lock
  Description
    Release an exclusive user lock on an application system
*/
--procedure release_application_lock(lockhandle in varchar2) is
procedure release_application_lock(lockhandle in varchar2 default cdapi.app_sys_lock_handle) is
  return_code integer;
  proc        varchar2(100) := 'Ck_Util.Release_application_lock. ';

begin

  return_code := dbms_lock.release (lockhandle);
  rmdbg.trace (proc || ' rc ' || to_char(return_code));

  if (return_code <> 0) then
    rmmes.post (cda_error, app_unlock_failed_err,lockhandle);
    raise unlock_failed;
  end if;

end release_application_lock;


/*----------------------------- Ora_Eerror ---------------------------------*/
/*
  Name 
    Ora_Eerror 
  Description
    Writes error stacks to the trace file.
  Notes
    Should be used in when other.
*/
procedure ora_error(utility_name in varchar2,proc in varchar2,rows in number) is
  location    varchar2(2500);
  error_stack varchar2(2000);
  call_stack  varchar2(2000);

begin
  error_stack := dbms_utility.format_error_stack; 
  call_stack  := dbms_utility.format_call_stack; 

  location := 'loc 900';
  rmdbg.trace(proc || location,rows,true);

  register(utility_name,'System error, rollback');

  location := 'loc 901. ';
  rmdbg.trace(proc || location || 'Error stack : ' || error_stack);
  location := 'loc 902. ';
  rmdbg.trace(proc || location || 'Call stack : '  || call_stack);
      
end ora_error;


/*---------------------------- Lock_Util -----------------------------------*/
/*
  Name 
    Lock_Util
  Description
    Lock a utility to run in singel user mode.
*/
procedure lock_util is

  lockhandle  varchar2(250);
  return_code integer;
  proc        varchar2(100) := 'Ck_Util.Lock_Util. ';

begin

  dbms_lock.allocate_unique ('D2K_UTIL', lockhandle);
  return_code := dbms_lock.request (lockhandle, 6, timeout, false);
  rmdbg.trace (proc || 'rc ' || to_char(return_code));
  rmdbg.flush_trace;
  if (return_code = 1) then
    rmmes.post (cda_error, timeout_failed_err);
    raise timeout_failed;
  elsif (return_code <> 0) then
    rmmes.post (cda_error, lock_failed_err);
    raise lock_failed;
  end if;

end lock_util;

/*---------------------------- Release_Util --------------------------------*/
/*
  Name 
    Release_Util
  Description
    Release the utility lock.
*/
procedure release_util is

  lockhandle  varchar2(250);
  return_code integer;
  proc        varchar2(100) := 'Ck_Util.Release_Util. ';

begin

  dbms_lock.allocate_unique ('D2K_UTIL', lockhandle);
  return_code := dbms_lock.release (lockhandle);
  rmdbg.trace (proc || ' rc ' || to_char(return_code));

end release_util;

/*---------------------- Create_DB_Link ------------------------------------*/
/*
  Name 
    Create_DB_Link
  Description
    Creates a database link.
*/
procedure create_db_link (user_name     in varchar2, 
                          password      in varchar2,
                          database_name in varchar2,
                          service_name in varchar2) is

  proc           varchar2(100) := 'Ck_Util.Create_DB_Link ';
  stmt           varchar2(300);
  cursor_pointer integer;
  
begin
     
  rmdbg.trace(proc||'loc 10');

  stmt := 'create database link ' || database_name || '@d2k connect to ' || 
           user_name || ' identified by ' || password || ' using ''' || 
           service_name || '''';

  cursor_pointer := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_pointer,stmt,dbms_sql.native);
  dbms_sql.close_cursor(cursor_pointer);

  rmdbg.trace(proc||'loc 11');

  exception
    when others then
      ck_util.ora_error (proc,proc,null);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      dbms_sql.close_cursor(cursor_pointer);
      rmdbg.trace (proc||'loc 12');
      raise;
  
end create_db_link;

/*------------------------- Drop_DB_Link -----------------------------------*/
/*
  Name 
    Drop_DB_Link
  Description
    Drops database link used to copy applications between repositories.
*/
procedure drop_db_link (database_name in varchar2) is

  proc              varchar2(100) := 'Ck_Util.Drop_DB_Link ';
  stmt              varchar2(300);
  cursor_pointer    integer;
  db_link_not_found exception;
  pragma exception_init(db_link_not_found,-2024);
  
begin

  rmdbg.trace(proc||'loc 13');

  stmt := 'drop database link ' || database_name || '@d2k';
  cursor_pointer := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_pointer,stmt,dbms_sql.native);
  dbms_sql.close_cursor(cursor_pointer);

  rmdbg.trace(proc||'loc 14');

  exception
    when db_link_not_found then
      dbms_sql.close_cursor(cursor_pointer);
      rmdbg.trace(proc||'loc 15');
      
    when others then
      dbms_sql.close_cursor(cursor_pointer);
      ck_util.ora_error (proc,proc,null);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      rmdbg.trace (proc||'loc 16');
      raise;

end drop_db_link;

end ck_util;  
/