--------------------------------------------------------
--  DDL for Package Body PLANTA_HELPERS
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PLANTA_HELPERS" 
as

procedure toggle_not_null_constraint
  ( p_table_name          in     dt415.di000216%type,
    p_column_name         in     dt412.di000215%type,
    p_null_toggle         in     varchar2)
is
  stmt                 varchar2(32767);
  l_nullable           varchar2(1);
  l_replace_sqlfield   varchar2(128);
  
begin
  if p_null_toggle = 'null' then -- set nullable
      l_nullable := 'N'; -- looking for previously not nullable columns to set null
      l_replace_sqlfield := 'update dt412 set di002461 = replace(replace(di002461, ''1'', ''0''), ''3'', ''2'')';
  else -- 'not null' -- set not nullable
      l_nullable := 'Y'; -- looking for previously nullable columns to set not null
      l_replace_sqlfield := 'update dt412 set di002461 = replace(replace(di002461, ''0'', ''1''), ''2'', ''3'')';
  end if;
  
  for c in (
    select
      table_name,
      column_name,
      data_type,
      data_length,
      data_precision,
      data_scale,
      (
        case
          when data_type = 'VARCHAR2' then
            data_type||'('||data_length||')'
          when data_type = 'NUMBER' then
            data_type||'('||data_precision||')'
          else
            data_type
          end
      ) as current_definition
    from user_tab_columns
    where nullable = l_nullable
    and upper(table_name) = upper(p_table_name)
    and upper(column_name) = upper(p_column_name)
  ) loop
    begin
      -- ddl
      stmt := 'alter table "'||c.table_name||'" modify "'||c.column_name||'" '||c.current_definition||' '||p_null_toggle;
      dbms_output.put_line(stmt);
      execute immediate stmt;

      -- system customizing
      stmt := l_replace_sqlfield;
      stmt := stmt || 'where di000215 = '''||c.column_name||''' and exists (';
      stmt := stmt || '  select 1 from dt415';
      stmt := stmt || '  where di000265 = di000239';
      stmt := stmt || '  and di000216 = '''||c.table_name||''' )';

      dbms_output.put_line(stmt);
      execute immediate stmt;
    end;
  end loop;
end toggle_not_null_constraint;


procedure disable_not_null_constraint
  ( p_table_name          in     dt415.di000216%type,
    p_column_name         in     dt412.di000215%type )
is
begin
  toggle_not_null_constraint(p_table_name, p_column_name, 'null');
end disable_not_null_constraint;


procedure enable_not_null_constraint
  ( p_table_name          in     dt415.di000216%type,
    p_column_name         in     dt412.di000215%type )
is
begin
  toggle_not_null_constraint(p_table_name, p_column_name, 'not null');
end enable_not_null_constraint;


end planta_helpers;
