------------------------------------------------------------------------------
-- To Execute and View results:
-- 
-- SQL> set serveroutput on
-- SQL> set serveroutput on size 100000
-- SQL> @owbrt_sys.sql
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
BEGIN
  DECLARE
    instance_no   number;
    owb_version   varchar2(1000);
    owb_home      varchar2(1000);
    delete_string varchar2(1000);
BEGIN
--  DBMS_OUTPUT.PUT_LINE('===============================================================');
--  DBMS_OUTPUT.PUT_LINE('owbrt_sys - Version 05/04/2007');
--  DBMS_OUTPUT.PUT_LINE('===============================================================');

   -- Get the instance version number
   SELECT 
     instance_number 
   INTO
      instance_no 
   FROM 
      v$instance;    

--  DBMS_OUTPUT.PUT_LINE('===============================================================');
--  DBMS_OUTPUT.PUT_LINE('Retrieved instance version number [' || instance_no || ']');
--  DBMS_OUTPUT.PUT_LINE('===============================================================');

  -- Select the current version 
  SELECT 
     property_value 
  INTO 
     owb_version 
  FROM 
     wb_rt_platform_properties
  WHERE 
     property_path = 'property.RuntimePlatform.0.version';

--  DBMS_OUTPUT.PUT_LINE('===============================================================');
--  DBMS_OUTPUT.PUT_LINE('Retrieved OWB version number [' || owb_version || ']');
--  DBMS_OUTPUT.PUT_LINE('===============================================================');

   -- Get the current owb home value
   owb_home := '%ORACLE_HOME%';

--  DBMS_OUTPUT.PUT_LINE('===============================================================');
--  DBMS_OUTPUT.PUT_LINE('Retrieved OWB home value [' || owb_home || ']');
--  DBMS_OUTPUT.PUT_LINE('===============================================================');

   delete_string := '%[' || instance_no || ']';

--  DBMS_OUTPUT.PUT_LINE('===============================================================');
--  DBMS_OUTPUT.PUT_LINE('Delete from table where value = ' || owb_home || ' and');
--  DBMS_OUTPUT.PUT_LINE('                        key like ' || delete_string || ';');
--  DBMS_OUTPUT.PUT_LINE('===============================================================');

   DELETE 
      FROM OWBRT_SYS.OWBRTPS 
      WHERE value = owb_home AND
            KEY like delete_string;

    INSERT 
       INTO OWBRT_SYS.OWBRTPS 
    VALUES 
       (owb_version || '[' || instance_no || ']',owb_home);
  EXCEPTION  
    WHEN DUP_VAL_ON_INDEX THEN  
      UPDATE 
         OWBRT_SYS.OWBRTPS 
      SET 
         value = owb_home 
      WHERE 
         key = owb_version || '[' || instance_no || ']';  
  END;
  commit;               
END;
/