alter session set current_schema = "ODMRSYS";

EXECUTE dbms_output.put_line('Start repository upgrade from 11.2.1.1.1 to 11.2.1.1.2. ' || systimestamp);

-- drop type <NAME> force  on all types that are being created in this script
-- this allows the script to be rerun without failing on type in use
set serveroutput on
DECLARE
w_name varchar2(200);
countObjects integer := 0;
countObjectsDropped integer := 0;
countObjectsFailedToDrop integer := 0;
sql_text varchar2(256);
Dynamic_Cursor integer; 
dummy integer; 
v_err_msg  VARCHAR2(4000);
purgeOption VARCHAR2(10);
repos_version VARCHAR2(30);

cursor type_set_cursor is
select type_name 
from all_types
where owner = 'ODMRSYS'
AND type_name IN ('ODMR_PREDICTION_SET_NUM_COSTF','ODMR_PREDICTION_SET_NUM_COSTD',
'ODMR_PREDICTION_SET_NUM_COSTN','ODMR_PREDICTION_SET_NUM_F',
'ODMR_PREDICTION_SET_NUM_D','ODMR_PREDICTION_SET_NUM_N',
'ODMR_PREDICTION_SET_CAT_COSTPD','ODMR_PREDICTION_SET_CATPD',
'ODMR_FEATURE_SET_NUMVD','ODMR_FEATURE_SET_CATVD','ODMR_CLUSTER_SET_NUMPD',
'ODMR_CLUSTER_SET_CATPD','ODMR_PREDICTION_NUM_COSTF','ODMR_PREDICTION_NUM_COSTD',
'ODMR_PREDICTION_NUM_COSTN','ODMR_PREDICTION_NUM_F','ODMR_PREDICTION_NUM_D',
'ODMR_PREDICTION_NUM_N','ODMR_PREDICTION_CAT_COSTPD','ODMR_PREDICTION_CATPD',
'ODMR_FEATURE_NUMVD','ODMR_FEATURE_CATVD','ODMR_CLUSTER_NUMPD','ODMR_CLUSTER_CATPD');


BEGIN
 DBMS_OUTPUT.ENABLE(NULL);

SELECT PROPERTY_STR_VALUE INTO repos_version FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES WHERE PROPERTY_NAME = 'VERSION';
IF ( repos_version = '11.2.1.1.1' ) THEN
    dbms_output.put_line('Upgrade will be performed: Drop old types in preparation for creating new versions.');
  open type_set_cursor;
  fetch type_set_cursor into w_name;
  while type_set_cursor%FOUND loop
  BEGIN
  
      sql_text := 'drop type  "ODMRSYS"."'|| w_name || '" FORCE ' ;
      DBMS_OUTPUT.PUT_LINE
         (sql_text ); 
      Dynamic_Cursor := dbms_sql.open_cursor;
      dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
      dummy := dbms_sql.execute(Dynamic_Cursor);
      dbms_sql.close_cursor(Dynamic_Cursor);
      countObjectsDropped := countObjectsDropped + 1;   
  EXCEPTION
  WHEN OTHERS THEN
          v_err_msg := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK(), 1, 4000);
          DBMS_OUTPUT.PUT_LINE ('Drop failed: ' || 'Owner.Object: ' || '"ODMRSYS"."' ||
          w_name || '"  Object Type: Type Error: ' || v_err_msg );
          countObjectsFailedToDrop := countObjectsFailedToDrop + 1;
          dbms_sql.close_cursor(Dynamic_Cursor);
  END;
  
     countObjects := countObjects + 1;
  fetch type_set_cursor into w_name;
  end loop;
  close type_set_cursor;
  
       DBMS_OUTPUT.PUT_LINE
         ('Total Number of Objects: ' || countObjects );  
       DBMS_OUTPUT.PUT_LINE
         ('Total Number of Objects Dropped: ' || countObjectsDropped ); 
       DBMS_OUTPUT.PUT_LINE
         ('Total Number of Objects Failed to Drop: ' || countObjectsFailedToDrop ); 
END IF;

END;
/




DECLARE
  repos_version VARCHAR2(30);
BEGIN
  SELECT PROPERTY_STR_VALUE INTO repos_version FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES WHERE PROPERTY_NAME = 'VERSION';
  IF ( repos_version = '11.2.1.1.1' ) THEN
    dbms_output.put_line('Create new types.');
    -- 12.1 Apply output column types
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTF
    AS OBJECT
    (
      PREDICTION  BINARY_FLOAT,
      PROBABILITY BINARY_DOUBLE,
      COST        BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTD
    AS OBJECT
    (
      PREDICTION  BINARY_DOUBLE,
      PROBABILITY BINARY_DOUBLE,
      COST        BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTN
    AS OBJECT
    (
      PREDICTION  NUMBER,
      PROBABILITY BINARY_DOUBLE,
      COST        BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_F
    AS OBJECT
    (
      PREDICTION  BINARY_FLOAT,
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_D
    AS OBJECT
    (
      PREDICTION  BINARY_DOUBLE,
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_N
    AS OBJECT
    (
      PREDICTION  NUMBER,
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CAT_COSTPD
    AS OBJECT
    (
      PREDICTION  VARCHAR2(128),
      PROBABILITY BINARY_DOUBLE,
      COST        BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CATPD
    AS OBJECT
    (
      PREDICTION  VARCHAR2(128),
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTF
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTF';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTD
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTN
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTN';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_F
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_F';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_D
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_D';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_N
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_N';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CAT_COSTPD
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_CAT_COSTPD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CATPD
    AS TABLE OF ODMRSYS.ODMR_PREDICTION_CATPD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_NUMVD
    AS OBJECT
    (
      FEATURE_ID  NUMBER,
      VALUE       BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_NUMVD
    AS TABLE OF ODMRSYS.ODMR_FEATURE_NUMVD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_CATVD
    AS OBJECT
    (
      FEATURE_ID  VARCHAR2(128),
      VALUE       BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_CATVD
    AS TABLE OF ODMRSYS.ODMR_FEATURE_CATVD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_NUMPD
    AS OBJECT
    (
      CLUSTER_ID  NUMBER,
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_NUMPD
    AS TABLE OF ODMRSYS.ODMR_CLUSTER_NUMPD';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_CATPD
    AS OBJECT
    (
      CLUSTER_ID  VARCHAR2(128),
      PROBABILITY BINARY_DOUBLE
    )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_CATPD
    AS TABLE OF ODMRSYS.ODMR_CLUSTER_CATPD';
    
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_COSTF FOR ODMRSYS.ODMR_PREDICTION_NUM_COSTF';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTF TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_COSTD FOR ODMRSYS.ODMR_PREDICTION_NUM_COSTD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_COSTN FOR ODMRSYS.ODMR_PREDICTION_NUM_COSTN';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTN TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_F FOR ODMRSYS.ODMR_PREDICTION_NUM_F';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_F TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_D FOR ODMRSYS.ODMR_PREDICTION_NUM_D';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_D TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_NUM_N FOR ODMRSYS.ODMR_PREDICTION_NUM_N';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_NUM_N TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_COSTF FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTF';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTF TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_COSTD FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_COSTN FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTN';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTN TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_F FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_F';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_F TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_D FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_D';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_D TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_NUM_N FOR ODMRSYS.ODMR_PREDICTION_SET_NUM_N';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_N TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_CAT_COSTPD FOR ODMRSYS.ODMR_PREDICTION_CAT_COSTPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_CAT_COSTPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_CATPD FOR ODMRSYS.ODMR_PREDICTION_CATPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_CATPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_CAT_COSTPD FOR ODMRSYS.ODMR_PREDICTION_SET_CAT_COSTPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_CAT_COSTPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_PREDICTION_SET_CATPD FOR ODMRSYS.ODMR_PREDICTION_SET_CATPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_PREDICTION_SET_CATPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_FEATURE_NUMVD FOR ODMRSYS.ODMR_FEATURE_NUMVD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_FEATURE_NUMVD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_FEATURE_SET_NUMVD FOR ODMRSYS.ODMR_FEATURE_SET_NUMVD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_FEATURE_SET_NUMVD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_FEATURE_CATVD FOR ODMRSYS.ODMR_FEATURE_CATVD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_FEATURE_CATVD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_FEATURE_SET_CATVD FOR ODMRSYS.ODMR_FEATURE_SET_CATVD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_FEATURE_SET_CATVD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_CLUSTER_NUMPD FOR ODMRSYS.ODMR_CLUSTER_NUMPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_CLUSTER_NUMPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_CLUSTER_SET_NUMPD FOR ODMRSYS.ODMR_CLUSTER_SET_NUMPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_CLUSTER_SET_NUMPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_CLUSTER_CATPD FOR ODMRSYS.ODMR_CLUSTER_CATPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_CLUSTER_CATPD TO PUBLIC';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_CLUSTER_SET_CATPD FOR ODMRSYS.ODMR_CLUSTER_SET_CATPD';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_CLUSTER_SET_CATPD TO PUBLIC';
    -- uptick the VERSION
    UPDATE ODMRSYS.ODMR$REPOSITORY_PROPERTIES SET PROPERTY_STR_VALUE = '11.2.1.1.2' WHERE PROPERTY_NAME = 'VERSION';
    COMMIT;  
    dbms_output.put_line('Repository version updated to  11.2.1.1.2.');
  ELSE
    dbms_output.put_line('Upgrade is not necessary.');
  END IF;  
END;
/

EXECUTE dbms_output.put_line('End repository upgrade from 11.2.1.1.1 to 11.2.1.1.2. ' || systimestamp);
