--
--   Upgrade script for "Schema Version Control for Oracle" (SVCO)
--   http://www.sumsoftsolutions.com
--
--   File Name: upgrade.sql
--   SVCO Version: 2.0.1 -> 3.0.0
--   DB: Oracle 11g and later
--   OS: non-specific
--   Comments: For details please see readme.txt
--

SET SERVEROUTPUT ON SIZE UNLIMITED
SET PAGESIZE 0
SET VERIFY OFF
WHENEVER OSERROR EXIT
WHENEVER SQLERROR EXIT

PROMPT .............................................................
PROMPT ...  Upgrading "Schema Version Control for Oracle" (SVCO) ...
PROMPT ...         http://www.sumsoftsolutions.com               ...
PROMPT ... PLEASE READ LICENSE AGREEMENT CAREFULLY               ...
PROMPT ...                 (file license.txt) BEFORE INSTALLING  ...
PROMPT .............................................................
PROMPT
PROMPT ... Checking upgrade requirements ...........................

declare
    v_ver   varchar2(64);
    v_comp  varchar2(64);
    v_count number;
begin	
	if  sys_context('USERENV', 'ISDBA') <> 'TRUE' then
		RAISE_APPLICATION_ERROR(-20700, '!!! Please login AS SYSDBA !!!');
	end if;	
	if upper(sys_context('USERENV', 'SESSION_USER')) <> 'SYS' then
		RAISE_APPLICATION_ERROR(-20700, '!!! Please login as SYS user !!!');
	end if;
	sys.dbms_utility.db_version(version => v_ver, compatibility => v_comp);
	if to_number(replace(v_ver, '.', null)) < 111060 then
		RAISE_APPLICATION_ERROR(-20700, '!!! Oracle version should be 11.1.0.6.0 or greater !!!');
  end if;
  select count(1)
  into v_count
  from all_users
  where username = 'SVCO';
  if v_count = 0 then
		RAISE_APPLICATION_ERROR(-20700, '!!! SVCO user does not exists, run install.sql !!!');
  end if;
  v_ver := SVCO.vcontrol_util.CurrentSVCOVersion;
  if to_number(replace(v_ver, '.', null)) <> 201 then
		RAISE_APPLICATION_ERROR(-20700, '!!! SVCO version should be 2.0.1 !!!');
  end if;
end;
/
-- new for Oracle 12c
GRANT SELECT ON SYS.V_$PARAMETER TO SVCO
/
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Stopping SVCO ...........................................
exec SVCO.VCONTROL_ADMIN.STOP_VERSION_CONTROL;
PROMPT ... Done ....................................................
PROMPT
ACCEPT tabtbs DEFAULT 'USERS' PROMPT 'Please enter TABLESPACE name for SVCO TABLES (Default is USERS) : '
ACCEPT idxtbs DEFAULT 'INDEXES' PROMPT 'Please enter TABLESPACE name for SVCO INDEXES (Default is INDEXES) : '
PROMPT
PROMPT ... Upgrading SVCO objects ..................................
@data/sequences_upgrade
@data/tables_upgrade
@data/exceptions_.spc
@data/vcontrol_util.spc
@data/vcontrol_util.bdy
@data/vcontrol_int.spc
@data/vcontrol_int.bdy
@data/vcontrol.spc
@data/vcontrol.bdy
@data/vcontrol_admin.spc
@data/vcontrol_admin.bdy
@data/views_upgrade
@data/grants_upgrade
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Starting SVCO ...........................................
exec SVCO.VCONTROL_ADMIN.START_VERSION_CONTROL;
PROMPT ... Done ....................................................
PROMPT
PROMPT ... All done ...
PROMPT
exit
