--------------------------------------------------------
--  DDL for Package Body PLANTA_TMP_TABLES
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PLANTA_TMP_TABLES" 
as

procedure truncate_
is
begin
  for tmp in (
    select table_name
    from user_tables
    where table_name in (
      'PLANTA_TABLES',
      'PLANTA_COLUMNS',
      'PLANTA_RELATIONS'
      )
    ) loop
      execute immediate 'truncate table '||tmp.table_name;
    end loop;

exception
  when others then
    null;

end truncate_;


procedure drop_
is
begin
  for tmp in (
    select table_name
    from user_tables
    where table_name in (
      'PLANTA_TABLES',
      'PLANTA_COLUMNS',
      'PLANTA_RELATIONS'
      )
    ) loop
      execute immediate 'truncate table '||tmp.table_name;
      execute immediate 'drop table '||tmp.table_name;
    end loop;

exception
  when others then
    null;

end drop_;


procedure create_
is
begin
  -- clean out previous
  drop_;

  execute immediate '
  create global temporary table planta_tables (
      id              varchar2(4)  not null, -- dt415.di000265%type,
      name            varchar2(30) not null, -- dt415.di000216%type,
      has_dummy       number(5)    not null, -- dt415.di004269%type,
      constraint pl_table_pk primary key (id)
  ) on commit preserve rows';
  execute immediate '
  create unique index planta_tables_by_name on planta_tables (name)';

  execute immediate '
  create global temporary table planta_columns (
      id              varchar2(6)  not null, -- dt412.di000238%type,
      name            varchar2(30)     null, -- dt412.di000215%type,
      table_id        varchar2(4)  not null, -- dt412.di000239%type,
      db_pos          number(5)    not null, -- dt412.di000241%type,
      sql_flags       number(5)    not null, -- dt412.di002461%type,
      constraint pl_col_pk primary key (id)
  ) on commit preserve rows';
  execute immediate '
  create index planta_columns_by_name on planta_columns (table_id, name)';
  execute immediate '
  create index planta_columns_name on planta_columns (name)';

  execute immediate '
  create bitmap index planta_columns_sql on planta_columns (sql_flags)';
  exception
  when others then
    execute immediate '
    create index planta_columns_sql on planta_columns (sql_flags)';

  execute immediate '
  create index planta_columns_dbpos on planta_columns (db_pos)';
  execute immediate '
  create index planta_columns_table_id on planta_columns (table_id)';

  execute immediate '
  create global temporary table planta_relations (
      table_id        varchar2(4)  not null, -- dt416.di000389%type,
      r_table_id      varchar2(4)  not null, -- dt416.di000277%type,
      relation        varchar2(4)  not null, -- dt416.di000279%type,
      recursion       number(5)    not null, -- dt416.di000374%type,
      name            varchar(16)      null, -- dt416.di000951%type,
      first_key_di    varchar(6)   not null, -- dt416.di000278%type,
      last_key_di     varchar(6)   not null, -- dt416.di000363%type,
      first_db_pos    number(5)    not null, -- dt412.di000241%type,
      last_db_pos     number(5)    not null, -- dt412.di000241%type,
      constraint pl_rel_pk primary key (table_id, r_table_id, first_key_di)
  ) on commit preserve rows';

end create_;


end planta_tmp_tables;
