ALTER session set current_schema = "ODMRSYS";

EXECUTE dbms_output.put_line('Start repository upgrade from 11.2.2.1.1 to 12.1.0.1.1. ' || systimestamp);

DECLARE
  sql_text varchar2(256);
  repos_version VARCHAR2(30);
  SYS_TABLE_NAME  VARCHAR2(30);
BEGIN
  SELECT PROPERTY_STR_VALUE INTO repos_version FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES WHERE PROPERTY_NAME = 'VERSION';
  IF ( repos_version = '11.2.2.1.1' ) THEN
  
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_ENGINE_DYNAMIC_NODE FOR ODMRSYS.ODMR_ENGINE_DYNAMIC_NODE';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAM_POINT_EX
                       AS OBJECT
                       (
                       ATTRIBUTE_NAME VARCHAR2(35),
                       ATTRIBUTE_VALUE VARCHAR2(32767),
                       GROUPING_ATTRIBUTE_NAME VARCHAR2(35),
                       GROUPING_ATTRIBUTE_VALUE VARCHAR2(32767),
                       ATTRIBUTE_PERCENT NUMBER
                       )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_HISTOGRAM_POINT_EX FOR ODMRSYS.ODMR_HISTOGRAM_POINT_EX';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_HISTOGRAM_POINT_EX TO PUBLIC';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAMS_EX
                       AS TABLE OF ODMRSYS.ODMR_HISTOGRAM_POINT_EX';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_HISTOGRAMS_EX FOR ODMRSYS.ODMR_HISTOGRAMS_EX';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_HISTOGRAMS_EX TO PUBLIC';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_VARCHAR2_EX
                       is TABLE OF VARCHAR2(32767)';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_NESTED_VARCHAR2_EX FOR ODMRSYS.ODMR_NESTED_VARCHAR2_EX';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_NESTED_VARCHAR2_EX TO PUBLIC';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_NVARCHAR2_EX
                       is TABLE OF NVARCHAR2(16383)';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_NESTED_NVARCHAR2_EX FOR ODMRSYS.ODMR_NESTED_NVARCHAR2_EX';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_NESTED_NVARCHAR2_EX TO PUBLIC';

    EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ODMR_OBJECT_VALUES FORCE';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE ODMRSYS.ODMR_OBJECT_VALUES 
                       is TABLE OF VARCHAR2(32767)';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_OBJECT_VALUES FOR ODMRSYS.ODMR_OBJECT_VALUES';
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ODMR_OBJECT_VALUES TO PUBLIC';

     -- uptick the VERSION
    UPDATE ODMRSYS.ODMR$REPOSITORY_PROPERTIES SET PROPERTY_STR_VALUE = '12.1.0.1.1' WHERE PROPERTY_NAME = 'VERSION';
    COMMIT;  
    dbms_output.put_line('Repository version updated to  12.1.0.1.1.');
  ELSE
    dbms_output.put_line('Upgrade is not necessary.');
  END IF;
  EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('Failure during upgrade: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
   RAISE;
END;
/

EXECUTE dbms_output.put_line('End repository upgrade from 11.2.2.1.1 to 12.1.0.1.1. ' || systimestamp);

