agentmail_utils.sql

set define off;

/*
  Dependencies from G/app/arcsql.sql (minimal set for agentmail: drop_table, does_table_exist, add_primary_key).
  Inserted here so agentmail can run standalone.
*/

-- drop procedure drop_object;
create or replace procedure drop_object (object_name varchar2, object_type varchar2) is
   -- | drop_object: Drop object_name of object_type if it exists.
   n number;
begin
   select count(*) into n
     from user_objects
    where object_name=upper(drop_object.object_name)
      and object_type=upper(drop_object.object_type);
   if n > 0 then
      if upper(drop_object.object_type) = 'TABLE' then
         execute immediate 'drop table '||upper(drop_object.object_name)||' cascade constraints purge';
      else
         execute immediate 'drop '||upper(drop_object.object_type)||' '||upper(drop_object.object_name);
      end if;
   end if;
exception
   when others then
      raise;
end;
/

-- drop function does_object_exist;
create or replace function does_object_exist (object_name varchar2, object_type varchar2) return boolean authid current_user is
   -- | does_object_exist: Return true if object_name of object_type exists.
   n number;
begin
   if upper(does_object_exist.object_type) = 'TYPE' then
      select count(*) into n
        from user_types
       where type_name=upper(does_object_exist.object_name);
   elsif upper(does_object_exist.object_type) = 'CONSTRAINT' then
      select count(*) into n
        from user_constraints
       where constraint_name=upper(does_object_exist.object_name);
   elsif upper(does_object_exist.object_type) = 'PACKAGE' then
      select count(*) into n
        from all_source
       where name=upper(does_object_exist.object_name)
         and type='PACKAGE';
   else
      select count(*) into n
        from user_objects
       where object_type = upper(does_object_exist.object_type)
         and object_name = upper(does_object_exist.object_name);
   end if;
   return n > 0;
end;
/

-- drop function does_table_exist;
create or replace function does_table_exist (table_name varchar2) return boolean is
begin
   if does_object_exist(does_table_exist.table_name, 'TABLE') then
      return true;
   else
      return false;
   end if;
end;
/

-- drop function does_constraint_exist;
create or replace function does_constraint_exist (constraint_name varchar2) return boolean is
   -- | does_constraint_exist: Return true if constraint_name exists.
begin
   if does_object_exist(does_constraint_exist.constraint_name, 'CONSTRAINT') then
      return true;
   else
      return false;
   end if;
exception
   when others then
   raise;
end;
/

-- drop procedure add_primary_key;
create or replace procedure add_primary_key (
   table_name in varchar2,
   column_name in varchar2) is
   -- | add_primary_key: Add primary key constraint to table_name with column_name if it doesn't exist.
begin
   if not does_constraint_exist('pk_'||table_name) then
      execute immediate 'alter table '||table_name||' add constraint pk_'||table_name||' primary key ('||column_name||')';
   end if;
end;
/

-- drop procedure drop_table;
create or replace procedure drop_table (table_name varchar2) is
   -- | drop_table: Drop table_name if it exists.
begin
   drop_object(drop_table.table_name, 'TABLE');
end;
/