/*
* Автор: Борисов Д.Г.
* Дата: 2013-07-15
* Задача: http://jira.softage.ru/jira/browse/SDFO-25
* Назначение: Изменения типа колонки CONTACT_PHONE таблицы MESS_CFM_ANNOUNCE_CONT и колонки MEMBER_PHONE
* Изменяются объекты:
		alter table MESS_CFM_ANNOUNCE_CONT
		Также обновляются шаблоны:
		    SFM\Информационное сообщение\TPL.2013-06-11.All.All.Enabled.151.xml
			SFM\Информационное сообщение\TPL.2013-06-11.Оператор ввода.Формирование сообщения.Enabled.150.xml
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2013-07-16.ChangeColumnType.sql';
	purpose       varchar2(4000) := 'Изменения типа колонки CONTACT_PHONE таблицы MESS_CFM_ANNOUNCE_CONT и колонки MEMBER_PHONE таблицы MESS_CFM_ANNOUNCE_MEMB';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	commit;

exception
	when others then
		rollback;
		raise;
end;

/
	/*
	****** Begin update script ******
	*/
		-- Не транзакционный апдейт
		-- Необходимы свои транзакции, если требуются

		create table mess_cfm_announce_cont_temp
		as
		select * from mess_cfm_announce_cont;
		truncate table mess_cfm_announce_cont;
		alter table mess_cfm_announce_cont modify contact_phone varchar2(300);
		insert into mess_cfm_announce_cont select * from mess_cfm_announce_cont_temp;
		commit;
		drop table mess_cfm_announce_cont_temp;

		create table mess_cfm_announce_memb_temp
		as
		select * from mess_cfm_announce_memb;
		alter table mess_cfm_announce_accounts disable constraints mess_cfm_account_fk;
		alter table mess_cfm_announce_found disable constraints mess_cfm_announce_ffk;
		truncate table mess_cfm_announce_memb;
		alter table mess_cfm_announce_memb modify member_phone varchar2(300);
		insert into mess_cfm_announce_memb select * from mess_cfm_announce_memb_temp;
		commit;
		alter table mess_cfm_announce_accounts enable constraints mess_cfm_account_fk;
		alter table mess_cfm_announce_found enable constraints mess_cfm_announce_ffk;
		drop table mess_cfm_announce_memb_temp;
	/*
	****** End update script ******
	*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
*/
