/* _up/epilogue.inc
 * Epiloog van update script van Facilitor, to be included in every schema update
 *
 * $Revision$
 * $Id$
 */
COMMIT;

DELETE FROM fac_session WHERE fac_session_expire < SYSDATE - 2;
DELETE FROM aut_client_perslid WHERE aut_client_perslid_login < SYSDATE - 60;
DELETE FROM fac_bookmark WHERE fac_bookmark_expire < SYSDATE - 2;
DELETE FROM web_user_messages WHERE web_user_mess_action_datum < SYSDATE - 60;
DELETE FROM fac_notificatie WHERE fac_notificatie_status=0
      AND fac_notificatie_systeemadres IS NULL;
COMMIT;

-- Dialect/terminologie items die niet meer bestaan
DELETE FROM fac_localeitems
      WHERE fac_localeitems_lcl NOT IN
                  (SELECT fac_locale_xsl_label FROM fac_locale_xsl);

-- Bij een DROP TABLE wordt nogal eens de PURGE vergeten
PURGE RECYCLEBIN;

-- Schema-naam uit MLD_T_UITVOERTIJD verwijderen.
--
-- data_default is van het datatype LONG. Daarop kan geen string-bewerking als INSTR(), SUBSTR() en REPLACE() worden gedaan.
-- Daarom moeten de benodigde gegevens eerst in een tijdelijke tabel ingelezen worden zodat het datatype VARCHAR2 wordt. Voor
-- het huidige doel (de owner van MLD_T_UITVOERTIJD verwijderen) is de stringlengte van 1000 voldoende.
-- Met FETCH .. BULK COLLECT INTO .. worden alle records die volgen uit de query in 1 keer opgehaald. Dit is sneller dan een fetch in een loop.
--
DECLARE
   TYPE ut_row IS RECORD
   (  tab_name     VARCHAR2(100)
   ,  col_name     VARCHAR2(100)
   ,  data_default VARCHAR2(1000)
   );
   TYPE ut_tab IS TABLE OF ut_row
      INDEX BY PLS_INTEGER;

   qry_txt      VARCHAR2(1000);
   upd_txt      VARCHAR2(1000);
   l_table      ut_tab;
   l_cursor     SYS_REFCURSOR;
   l_pos        INTEGER;
   l_default    VARCHAR2(1000);
BEGIN
   qry_txt := 'SELECT table_name, column_name, data_default'
           || '  FROM user_tab_columns'
           || ' WHERE data_type = ''MLD_T_UITVOERTIJD''';
   OPEN l_cursor FOR qry_txt;
   FETCH l_cursor BULK COLLECT INTO l_table;
   CLOSE l_cursor;

   FOR indx IN 1 .. l_table.COUNT
   LOOP
      l_pos := INSTR(l_table(indx).data_default, '"."');
      --dbms_output.put_line('A: ' || SUBSTR(l_table(indx).data_default,1,50));
      IF (l_pos > 0)
      THEN
         l_default := REPLACE(REPLACE(SUBSTR(l_table(indx).data_default, l_pos+3, 100), '"', ''),'''','''''');
         upd_txt := 'UPDATE user_tab_columns'
                 || '   SET data_default = ''' || l_default || ''''
                 || ' WHERE table_name =   ''' || l_table(indx).tab_name || ''''
                 || '   AND column_name =  ''' || l_table(indx).col_name || '''';
         --dbms_output.put_line('B: ' || upd_txt);
         EXECUTE IMMEDIATE upd_txt;
      END IF;
   END LOOP;
END;
/

///////////////////////////// now recreate all views, triggers etc acc to the latest state ///////
#include "recreate.inc"

#ifdef POST_RECREATE
#include POST_RECREATE
#endif

select 'Upgrade has completed in ' || ROUND(( dbms_utility.get_time - :update_start )/100) || ' seconds.' from dual;

/* Report invalid objects, if any */
SET ECHO OFF
SET HEADING OFF
SET LINESIZE 132
PROMPT List of all invalid objects after Facilitor upgrade.
PROMPT Some errors may resolve by running the customer specific script next.
SELECT 'OR'||'A-'||'DB'||_DBV_STRING||' Warning: ' || object_type || ' ' || object_name || ' is ' || status
  FROM user_objects
 WHERE object_type IN ('VIEW', 'TRIGGER', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
   AND status <> 'VALID'
 ORDER BY object_name;

SET ECHO OFF
SET FEEDBACK OFF
PROMPT ======================================================================
SELECT 'Schema Facilitor@' || USER || ' is now ' || fac.getdbversion() FROM DUAL;
PROMPT
PROMPT Your schema upgrade is ready. Please check the logfile for errors.
PROMPT If in doubt please contact: Facilitor Helpdesk +31 53 4800 700
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile
SPOOL OFF
