/*
  ****** Инструкции для 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-26.AddIsBankOffshore.sql';
	purpose       varchar2(4000) := 'Добавление колонок MEMBER_BANK_IS_OFFSHORE, FOUND_COUNT_TEMP таблицы MESS_CFM_ANNOUNCE_MEMB';
	script_exists number := 0;
	
	v_column_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;

	/*
	****** Begin update script ******
	*/
	select count(*) into v_column_exists
		from user_tab_cols c
		where c.column_name = 'MEMBER_BANK_IS_OFFSHORE'
			and c.table_name = 'MESS_CFM_ANNOUNCE_MEMB';
	
	if (v_column_exists = 0) then
		execute immediate 'ALTER TABLE MESS_CFM_ANNOUNCE_MEMB ADD MEMBER_BANK_IS_OFFSHORE number';
		execute immediate 'COMMENT ON COLUMN "KFM"."MESS_CFM_ANNOUNCE_MEMB"."MEMBER_BANK_IS_OFFSHORE" IS ''Признак нахождения банка участника в оффшорной зоне''';
	end if;

	select count(*) into v_column_exists
		from user_tab_cols c
		where c.column_name = 'FOUND_COUNT_TEMP'
			and c.table_name = 'MESS_CFM_ANNOUNCE_MEMB';
	
	if (v_column_exists = 0) then
		execute immediate 'ALTER TABLE MESS_CFM_ANNOUNCE_MEMB ADD FOUND_COUNT_TEMP number';
		execute immediate 'COMMENT ON COLUMN "KFM"."MESS_CFM_ANNOUNCE_MEMB"."FOUND_COUNT_TEMP" IS ''Технологическое поле для контроля заполнения 4.9''';
	end if;
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;

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