CREATE OR REPLACE PACKAGE BODY UpdateReposUsers AS

TYPE TTYPE is REF CURSOR;

------------------------------------------------------------------------------
-- To Execute and View results:
-- 
-- SQL> set serveroutput on
-- SQL> set serveroutput on size 100000
-- SQL> exec UpdateReposUsers.UpdReposUsers <Repository Owner> <new version number>
-- SQL> exec UpdateReposUsers.UpdReposUsers OWB_REPOS '10.2.0.3'
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Debug messages
------------------------------------------------------------------------------
PROCEDURE MyDebug(debugMessage VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(debugMessage);
END MyDebug;

------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE UpdReposUsers(repositoryOwner VARCHAR2, newVersion VARCHAR2)
IS
  tcursor TTYPE;
  RTUserName ALL_SYNONYMS.owner%TYPE;
  upperReposOwner VARCHAR2(1000);
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

--  MyDebug('===============================================================');
--  MyDebug('UpdateReposUsers - Version 03/28/2007');
--  MyDebug('===============================================================');
--  MyDebug('Working on Runtime User : ' || repositoryOwner );


   -- convert repository owner to uppercase
   select
      upper(repositoryOwner)
   into
      upperReposOwner
   from
      dual;

--  MyDebug('===============================================================');
--  MyDebug('Before : ' || repositoryOwner );
--  MyDebug('After  : ' || upperReposOwner );
--  MyDebug('===============================================================');

  open tcursor for 
    select 
      owner
    from 
      ALL_SYNONYMS
    where table_owner = repositoryOwner and
               synonym_name = 'ALL_RT_LOCATIONS';

   --  MyDebug('Working on Runtime User : ' || upperReposOwner );

    loop
      begin
        fetch tcursor into RTUserName;
        exit when tcursor%NOTFOUND;

--        MyDebug('Found Runtime User : ' || RTUserName);

        doUpdateReposUsers(upperReposOwner, RTUserName, newVersion);
        commit;
      end;
    end loop;
    close tcursor;

    --after everything is fine, we need to commit the changes
    COMMIT;
--    MyDebug('===============================================================');
--    MyDebug('Completed successfully.');
--    MyDebug('===============================================================');

  exception
    when others then
    ROLLBACK;

    raise_application_error(-20001, 'UpdateReposUsers.UpdateReposUsers encoutered the SQL error:'||SUBSTR(SQLERRM,1,200));

END UpdReposUsers;

------------------------------------------------------------------------------
-- For every map name passed in here, we want to make sure that we have the
-- same number of input attributes as output attributes.
------------------------------------------------------------------------------
PROCEDURE doUpdateReposUsers(repositoryOwner VARCHAR2, RTowner ALL_SYNONYMS.owner%TYPE, newVersion VARCHAR2)
IS
t_statement VARCHAR2(1000);
t_statement2 VARCHAR2(1000);
isFound integer := 0;
BEGIN
   select
     1
   into
     isFound
   from
      sys.all_all_tables 
   where table_name = 'WB_RT_VERSION_FLAG' and owner = RTowner ;

   if (isFound = 1) then
      -- Form the update statement
      t_statement := 'update ' || RTowner || '.WB_RT_VERSION_FLAG set version = ''' || newVersion || ''' ';

--     MyDebug('===============================================================');
--     MyDebug( t_statement);
--     MyDebug('===============================================================');
      execute immediate t_statement;

      -- Form the create synonym statement
      t_statement2 := 'create synonym ' || RTowner || '.WB_RT_DP_CREATE_SAMPLE FOR ' || repositoryOwner || '.WB_RT_DP_CREATE_SAMPLE';

--     MyDebug('===============================================================');
--     MyDebug( t_statement2);
--     MyDebug('===============================================================');
      execute immediate t_statement2;

   end if;

  exception
    when others then
    ROLLBACK;

    raise_application_error(-20001, 'doUpdateReposUsers.UpdateReposUsers encoutered the SQL error:'||SUBSTR(SQLERRM,1,200));

END doUpdateReposUsers;
                              
END UpdateReposUsers;
/