DECLARE
  type t_string IS TABLE OF VARCHAR2(50);
  t_table_name t_string        := t_string('MST_DOX_STAGE');

  type t_column IS TABLE OF  t_string;
  t_columns_name t_column        := t_column( 
                                t_string('AGREEMENT_TEMPLATE', 'VARCHAR2(100 CHAR) DEFAULT ''Unknown'' NOT NULL' )
								t_string('REMEDIATION_REG_TYPE', 'VARCHAR2(4 CHAR)')
                                );
  
  V_TABLE_OWNER      VARCHAR2(100) := 'MOUSETRAP';
  v_table_name       VARCHAR2(100);
  v_Column_Name      VARCHAR2(100);
  v_type_Name         VARCHAR2(100);
  v_alter_table_stmt VARCHAR2(3000);
  v_cnt              NUMBER;
  v_sel_qry          VARCHAR2(1000);
BEGIN
  FOR i IN 1..t_table_name.count
  LOOP
    v_table_name := t_table_name(i);
    v_sel_qry    := 'SELECT COUNT(TABLE_NAME) FROM ALL_TABLES WHERE OWNER = :tblowner AND TABLE_NAME = :tblname';
    EXECUTE IMMEDIATE v_sel_qry INTO v_cnt USING v_Table_Owner,v_Table_Name;
    
    IF v_cnt = 1 THEN
      
    
    /*ADDING COLUMN*/
        FOR i IN 1..t_columns_name.count LOOP    
            v_Column_Name := t_columns_name(i)(1);
            v_type_Name   := t_columns_name(i)(2);
            v_sel_qry     := 'SELECT COUNT(COLUMN_NAME) FROM ALL_TAB_COLUMNS WHERE OWNER = :tblowner AND TABLE_NAME = :tblname AND COLUMN_NAME = :colname';
            EXECUTE IMMEDIATE v_sel_qry INTO v_cnt USING v_Table_Owner,v_Table_Name, v_Column_Name;
            IF v_cnt              = 0 THEN
			
			IF v_Column_Name := 'AGREEMENT_TEMPLATE' 
			THEN
			v_alter_table_stmt := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' ADD ('||v_Column_Name||' ' || v_type_Name ||' )';
            EXECUTE IMMEDIATE v_alter_table_stmt;
			END IF;


		
			IF v_Column_Name := 'REMEDIATION_REG_TYPE'
			THEN			
			v_alter_table_stmt := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' MODIFY ('||v_Column_Name||' DEFAULT NULL )';
			EXECUTE IMMEDIATE v_alter_table_stmt;
			END IF;

				
				DBMS_OUTPUT.PUT_LINE('Table '|| v_Table_Owner||'.'||v_Table_Name||' column '|| v_Column_Name || ' type ' || v_type_Name || ' added');
            END IF;
        END LOOP;
      /*ADDING COLUMN*/
      
    
    ELSE
      DBMS_OUTPUT.PUT_LINE('Table '|| v_Table_Owner||'.'||v_Table_Name||' not exists');
    END IF;
  END LOOP;
END;
/

IF v_cnt  = 0 and t_columns_name(i)(1) = 'AGREEMENT_TEMPLATE' THEN
			v_alter_table_stmt01 := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' ADD ('||v_Column_Name||' ' || v_type_Name ||' )';
            EXECUTE IMMEDIATE v_alter_table_stmt01;
			ELSE 
			DBMS_OUTPUT.PUT_LINE('1');
			END IF;

			IF v_cnt = 0 and t_columns_name(i)(2) = 'REMEDIATION_REG_TYPE'
			THEN			
			v_alter_table_stmt02 := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' MODIFY ('||v_Column_Name||' DEFAULT NULL )';
			EXECUTE IMMEDIATE v_alter_table_stmt02;
			ELSE 
			DBMS_OUTPUT.PUT_LINE('2');
			END IF;