CREATE OR REPLACE PACKAGE BODY fix_5658484 AS

TYPE TTYPE is REF CURSOR;

------------------------------------------------------------------------------
-- To Execute and View results:
-- 
-- SQL> set serveroutput on
-- SQL> set serveroutput on size 100000
-- SQL> exec fix_5658484.do_fix_5658484 <Repository Owner>
-- SQL> exec fix_5658484.do_fix_5658484('OWB_REPOS')
------------------------------------------------------------------------------

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

------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE do_fix_5658484 (repositoryOwner VARCHAR2)
IS
  upperReposOwner VARCHAR2(1000);
  index_tablespace VARCHAR2(1000);
  t_statement VARCHAR2(1000);
  t_statement2 VARCHAR2(1000);
  isFound integer := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

--  MyDebug('===============================================================');
--  MyDebug('do_fix_5658484 - Version 04/25/2007');
--  MyDebug('===============================================================');
--  MyDebug('Working on Repository User : ' || repositoryOwner );


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

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

   select
     1
   into
     isFound
   from 
      sys.dba_indexes 
   where index_name = 'WB_RT_IDX_AP_AE' and owner = upperReposOwner;

   if (isFound = 1) then

      -- Find the index tablespace name
     select
        tablespace_name
     into
        index_tablespace 
     from 
        sys.dba_indexes 
     where 
        index_name = 'WB_RT_IDX_AP_AE' and owner = upperReposOwner;

      -- Form the first create index statement
     t_statement := 'create index ' || upperReposOwner || '.wb_rt_idx_ao_ou on ' || upperReposOwner || '.wb_rt_audit_objects (object_uoid) tablespace ' || index_tablespace || ' ';
      -- Form the second create index statement
     t_statement2 := 'create index ' || upperReposOwner || '.wb_rt_idx_ao_pa on ' || upperReposOwner || '.wb_rt_audit_objects (parent_audit_object_id) tablespace ' || index_tablespace || ' ';

     -- Create the indexes 
--     MyDebug('===============================================================');
--     MyDebug( t_statement);
--     MyDebug('===============================================================');
     execute immediate t_statement;

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

   end if;

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

  exception
    when others then
    ROLLBACK;
    MyDebug('Indexes already exist.');
    --raise_application_error(-20001, 'fix_5658484.do_fix_5658484 encoutered the SQL error:'||SUBSTR(SQLERRM,1,200));

END do_fix_5658484;
                              
END fix_5658484;
/