--=============================================================================
-- <SOURCE
-- Company  = "Totvs S.A."
-- Project  = "TOTVS Tec"
-- Type     = "Script"
-- Name     = "ifxTemptabs_JOB_ORA.sql"
-- Comment  = "Creates the procedure and job to handle temp tables in ORACLE."
-- Language = "PL/SQL"
-- />
--
--
-- <DESCRIPTION> <![CDATA[
--
-- This script creates a JOB and a PL/SQL procedure to maintain
-- pseudo temporary tables in and Oracle database.
--
-- IMPORTANT WARNING :
--    The owner of this procedure must have the following privileges assigned
--    directly ( not trough roles ) :
--        GRANT DROP ANY TABLE TO <admin_user>
--        GRANT DROP ANY SEQUENCE TO <admin_user>
--        GRANT SELECT ON SYS.V_$SESSION TO <admin_user>
--
-- The main purpose of this procedure and JOB is to DROP the "temporary tables"
-- which are no more used by the programs.
-- Programs are responsible of temporary table creation and destruction.
-- Programs should normally "unregister" temporary tables which are dropped,
-- but this might not happen if the program crashes.
--
-- Observation:
-- Can use this script only after technology TOTVS Tec - BUILD 2011.
--
-- ]]></DESCRIPTION>
--
-- </SOURCE>
--=============================================================================

--
-- Properly modify the connection string.
--

--connect logix;

create or replace trigger drop_temp_table_by_session
before logoff on schema
begin
  for c in (select table_name
              from user_tables
             where tablespace_name IS NULL
               and table_name      like 'T'||SubStr(LPad(UserEnv('SESSIONID'),38,'0'),-6,6)||'%') loop

       -- Drop the table
       begin
          execute immediate 'drop table '||c.table_name||' purge';
       exception
           when others then null;
       end;

       -- Drop the sequence ( serial column )
       begin
           execute immediate 'drop sequence '||c.table_name||'_srl';
       exception
           when others then null;
       end;
  end loop;
exception
  when others then raise;
end;
/
--

create or replace procedure drop_orphan_tables_by_session
is
begin
   -- utilizada a função LENGTH(TRIM(TRANSLATE('<sequencia_de_caracteres>', ' +-.0123456789', ' '))) is null
   -- para testar se a sequencia de caracteres que compoe parte do nome dos objetos é um número, para não
   -- eliminar outros objetos que não sejam tabelas temporárias
   
   -- drop the table like T%
   for c in (select t.table_name
               from user_tables t
              where t.table_name like 'T%' 
                and LENGTH(TRIM(TRANSLATE(substr(t.table_name,2,6), ' +-.0123456789', ' '))) is null
				and t.tablespace_name IS NULL
                and not exists(select 1
                                 from v_sessao_ora v
                                where SubStr(LPad(v.sid,38,'0'),-6,6) = substr(t.table_name,2,6)) ) LOOP
       begin
          execute immediate 'drop table '||c.table_name||' purge';
       exception
           when others then null;
       end;
   end loop;

   -- drop the table like TT%
   for c in (select t.table_name
               from user_tables t
              where t.table_name like 'TT%' 
                and t.tablespace_name = 'TEMPTABS'
                and LENGTH(TRIM(TRANSLATE(substr(t.table_name,3,5), ' +-.0123456789', ' '))) is null 
                and not exists(select 1
                                 from v_sessao_ora v
                                where SubStr(LPad(v.sid,38,'0'),-5,5) = substr(t.table_name,3,5)) ) LOOP
       begin
          execute immediate 'drop table '||c.table_name||' purge';
       exception
           when others then null;
       end;
   end loop;
   
   -- drop the sequence like T% ( serial column )
   for c in (select s.sequence_name
               from user_sequences s
              where s.sequence_name like 'T%' 
                and LENGTH(TRIM(TRANSLATE(substr(s.sequence_name,2,6), ' +-.0123456789', ' '))) is null
                and not exists(select 1
                                 from v_sessao_ora v
                                where SubStr(LPad(v.sid,38,'0'),-6,6) = substr(s.sequence_name,2,6)) ) LOOP
       begin
           execute immediate 'drop sequence '||c.sequence_name;
       exception
           when others then null;
       end;
   end loop;

   -- drop the sequence like TT% ( serial column )
   for c in (select s.sequence_name
               from user_sequences s
              where s.sequence_name like 'TT%' 
                and LENGTH(TRIM(TRANSLATE(substr(s.sequence_name,3,5), ' +-.0123456789', ' '))) is null
                and not exists(select 1
                                 from v_sessao_ora v
                                where SubStr(LPad(v.sid,38,'0'),-5,5) = substr(s.sequence_name,3,5)) ) LOOP
       begin
           execute immediate 'drop sequence '||c.sequence_name;
       exception
           when others then null;
       end;
   end loop;
   
exception
   when others then null;
end;
/
--
-- Properly modify the connection string. Attention to the owner of the procedure DROP_ORPHAN_TABLES_BY_SESSION.
-- Modify it also in the parameter job_action.
--

connect SYS as SYSDBA;

BEGIN
	DBMS_SCHEDULER.CREATE_JOB (
		 job_name => 'LOGIX.T_DROP_ORPHAN_TABS_BY_SESSION'
		,job_type => 'PLSQL_BLOCK'
		,job_action => 'LOGIX.DROP_ORPHAN_TABLES_BY_SESSION;'
		,start_date => sysdate
		,repeat_interval => 'FREQ=DAILY;INTERVAL=2;BYHOUR=23;BYMINUTE=0;BYSECOND=0'
  	,enabled => TRUE
		,comments => 'Remove tabelas temporarias do LOGIX'
);
END;