rem gfc_temp_table_type.sql
rem (c)2009-10 Go-Faster Consultancy Ltd. 
rem www.go-faster.co.uk

rem Trigger to switch non-shared instance of working storage table from normal to global 
rem temporary if restart disabled, and to switch it back if it restart not disabled.
rem the permanent table is also truncated on switch
rem 1.3.2010 added control table to select AEs for which GTT conversion to be enabled.

----------------------------------------------------------------------------------------------------
--This trigger requires 
-- functions from the psftapi package
-- wrapper script and change to the DDL model must be fitted to suppress stats collection on GTTs.
------------------------------------------------------------------------------------------------
set echo on serveroutput on 
ROLLBACK
/
----------------------------------------------------------------------------------------------------
-- SYSADM must have explicit CREATE TABLE privilege, not simply via a role
----------------------------------------------------------------------------------------------------
GRANT CREATE TABLE TO SYSADM;
----------------------------------------------------------------------------------------------------
--This record should be created in Application Designer
------------------------------------------------------------------------------------------------
CREATE TABLE PS_GFC_TEMPTBLCNTL 
(AE_APPLID VARCHAR2(12) NOT NULL
,GFC_GLOBALTEMPTBL VARCHAR2(1) NOT NULL
) TABLESPACE PTTBL 
/
CREATE UNIQUE  iNDEX PS_GFC_TEMPTBLCNTL ON PS_GFC_TEMPTBLCNTL
(AE_APPLID
) TABLESPACE PSINDEX 
/
------------------------------------------------------------------------------------------------
--populate control table with non-restartable non-aelibrary processes
------------------------------------------------------------------------------------------------
INSERT INTO ps_gfc_temptblcntl
(ae_applid, gfc_globaltemptbl)
SELECT ae_applid, 'Y'
FROM psaeappldefn --psaeappltemptbl
WHERE ae_appllibrary = 'N'
AND ae_disable_restart = 'Y'
AND ae_applid IN('GPGB_EDI','GPGB_EDI_PRC')
MINUS
SELECT *
FROM ps_gfc_temptblcntl
/
COMMIT
/

SELECT *
FROM ps_gfc_temptblcntl
/
----------------------------------------------------------------------------------------------------
ALTER TRIGGER gfc_deletetemptablestats DISABLE
/
DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/
ALTER TRIGGER gfc_deletetemptablestats ENABLE
/
----------------------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER gfc_temp_table_type
BEFORE INSERT ON sysadm.ps_aetemptblmgr
FOR EACH ROW
WHEN (new.curtempinstance > 0) /*don't fire for shared instance*/
DECLARE
 l_ps_table_name    VARCHAR2(30);
 l_ps_temporary     VARCHAR2(1);
 l_ps_found         BOOLEAN;

 l_gt_table_name    VARCHAR2(30);
 l_gt_temporary     VARCHAR2(1);
 l_gt_found         BOOLEAN;

 l_xx_table_name    VARCHAR2(30);
 l_xx_temporary     VARCHAR2(1);
 l_xx_found         BOOLEAN;

 l_gt_control       VARCHAR2(1); /*to hold control instruction for this AE, Y=build GTTs, N=revert to normal*/

 l_clob             CLOB;
 l_cmd              VARCHAR2(32767);
 l_rename1          VARCHAR2(100);
 l_rename2          VARCHAR2(100);
 l_recover          VARCHAR2(100);
 l_truncate         VARCHAR2(100);

 l_errno            INTEGER := -20000; /* set a valid default in case of error in trigger*/
 e_no_error         EXCEPTION;
 l_message          VARCHAR2(200) := 'No Message';
 e_error_message    EXCEPTION;
 e_warning_message  EXCEPTION;

 PRAGMA autonomous_transaction;

 CURSOR c_table (p_table_name VARCHAR2) IS
 SELECT dbms_metadata.get_ddl('TABLE',table_name)
 FROM   user_tables
 WHERE  table_name = p_table_name
 ;

 CURSOR c_indexes (p_table_name VARCHAR2) IS
 SELECT dbms_metadata.get_ddl('INDEX',index_name)
 FROM   user_indexes
 WHERE  table_name = p_table_name
 AND    index_type IN ('NORMAL','FUNCTION-BASED NORMAL') --!LOB
 ;

BEGIN

 IF :new.ae_disable_restart = 'N' THEN
  l_gt_control := 'N';
 ELSE
  BEGIN
   /*check that table exists*/
   SELECT DECODE(gfc_globaltemptbl,'Y','Y','N')
   INTO   l_gt_control
   FROM   ps_gfc_temptblcntl
   WHERE  ae_applid = :new.ae_applid
   ;
  EXCEPTION
   WHEN no_data_found THEN 
    l_gt_control := 'N'; --if no control record found assume use permanent tables
  END;
 END IF;

 BEGIN
  /*check that table exists*/
  SELECT table_name, temporary
  INTO   l_ps_table_name
  ,      l_ps_temporary
  FROM   user_tables t
  ,      psrecdefn r
  WHERE  r.recname = :new.recname
  AND    t.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||:new.curtempinstance
  ;
  l_ps_found := TRUE;
 EXCEPTION
  WHEN no_data_found THEN 
   l_ps_found := FALSE;
   l_message := 'Cannot find table for record '||:new.recname||', instance '||:new.curtempinstance;
   l_errno := -20001;
   RAISE e_error_message;
 END;

 /*check if status of temp table and also matches control table is correct then stop now because everything is fine*/
 --dbms_output.put_line('l_ps_temporary    ='||l_ps_temporary);
 --dbms_output.put_line('l_gt_control      ='||l_gt_control);
 IF l_ps_temporary = l_gt_control THEN
  --dbms_output.put_line('RAISE e_no_error');
  RAISE e_no_error;
 END IF;

 BEGIN /*check whether GTT table exists*/
  l_gt_table_name := 'GT_'||:new.recname||:new.curtempinstance;

  SELECT temporary
  INTO   l_gt_temporary
  FROM   user_tables t
  WHERE  t.table_name = l_gt_table_name
  ;
  l_gt_found := TRUE;
 EXCEPTION
  WHEN no_data_found THEN 
   l_gt_found := FALSE;
 END;

 BEGIN /*check whether backup of normal table exists*/
  l_xx_table_name := 'XX_'||:new.recname||:new.curtempinstance;

  SELECT temporary
  INTO   l_xx_temporary
  FROM   user_tables t
  WHERE  t.table_name = l_xx_table_name
  ;
  l_xx_found := TRUE;
 EXCEPTION
  WHEN no_data_found THEN 
   l_xx_found := FALSE;
 END;

 IF l_xx_found AND l_gt_found AND l_ps_found THEN
  IF l_ps_temporary = 'N' THEN
   l_cmd := 'DROP TABLE '||l_gt_table_name;  /*so we should drop the GT and reset some flags*/
  psftapi.message_log('DDL (len='||LENGTH(l_cmd)||'):'||l_cmd,0,TRUE);
  --dbms_output.put_line('l_cmd='||l_cmd);
  EXECUTE IMMEDIATE l_cmd;
   l_gt_found := FALSE;    
   l_gt_temporary := '';

  ELSE  /*otherwise if all three tables exist then something is seriously wrong and we should go no further*/
   l_message := 'Tables '||l_ps_table_name||', '||l_gt_table_name||' and '||l_xx_table_name||' all exist, but should not.';
   l_errno := -20002;
   RAISE e_error_message;
  END IF;
 END IF;

 /*if the XX exists, and the PS is not temporary then someone has probably rebuilt the PS with App Designer*/
 IF l_xx_found AND l_ps_found AND l_ps_temporary = 'N' THEN
  l_cmd := 'DROP TABLE '||l_xx_table_name;  /*so we should drop the XX and reset some flags*/
  psftapi.message_log('DDL (len='||LENGTH(l_cmd)||'):'||l_cmd,0,TRUE);
  --dbms_output.put_line('l_cmd='||l_cmd);
  EXECUTE IMMEDIATE l_cmd;
  l_xx_found := FALSE;    
  l_xx_temporary := '';
 END IF;

 /*if XX and GT both exist but PS doesn't exist*/
 IF l_xx_found AND l_gt_found AND NOT l_ps_found THEN
  IF l_gt_control = 'Y' THEN 
   l_rename1 := 'ALTER TABLE '||l_gt_table_name||' RENAME TO '||l_ps_table_name;
   l_gt_found := FALSE;
   l_gt_temporary := '';
  ELSE
   l_rename1 := 'ALTER TABLE '||l_xx_table_name||' RENAME TO '||l_ps_table_name;
   l_xx_found := FALSE;
   l_xx_temporary := '';
  END IF;
  EXECUTE IMMEDIATE l_rename1;
  l_ps_found := TRUE;
  l_ps_temporary := l_gt_control;
 END IF;

 --need to change to permanent table to temporary
 IF l_gt_control = 'Y' AND l_ps_temporary = 'N' THEN
  --dbms_output.put_line('Change Permanent to Temporary');
  IF l_xx_found THEN
   l_message := 'Cannot move '||l_ps_table_name||' to '||l_xx_table_name||', because it already exists';
   l_errno := -20003;
   RAISE e_error_message;
  ELSE
   l_rename1  := 'ALTER TABLE '||l_ps_table_name||' RENAME TO '||l_xx_table_name;
   l_recover  := 'ALTER TABLE '||l_xx_table_name||' RENAME TO '||l_ps_table_name;
   l_truncate := 'TRUNCATE TABLE '||l_xx_table_name;
  END IF;

  IF l_gt_found THEN
   IF l_gt_temporary = 'N' THEN
    l_message := 'Table '||l_gt_table_name||' should be a Global Temporary Table.';
    l_errno := -20004;
    RAISE e_error_message;
   ELSE
    l_rename2 := 'ALTER TABLE '||l_gt_table_name||' RENAME TO '||l_ps_table_name;
   END IF;
  ELSE --now we need to create GTT
   --GTTs do not support storage, tablespace, or segment options
   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',FALSE);
   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',FALSE);

   OPEN c_table(l_ps_table_name);
   FETCH c_table INTO l_clob;
   l_cmd := dbms_lob.substr(l_clob);
   IF LENGTH(l_cmd) > 32760 THEN
    l_message := 'Generated DDL too long.  Global Temporary Table '||l_gt_table_name||' must be created manually.';
    l_errno := 5;
    RAISE e_warning_message;
   ELSE
    l_cmd := REPLACE(REPLACE(l_cmd,'CREATE TABLE "','CREATE GLOBAL TEMPORARY TABLE "'),'"."PS_','"."GT_')||' ON COMMIT PRESERVE ROWS';
    psftapi.message_log('DDL (len='||LENGTH(l_cmd)||'):'||l_cmd,0,TRUE);
    EXECUTE IMMEDIATE l_cmd;
   END IF;
   CLOSE c_table;

   dbms_stats.lock_table_stats(ownname => user, tabname => l_gt_table_name);

   OPEN c_indexes(l_ps_table_name);
   LOOP
    FETCH c_indexes INTO l_clob;
    EXIT WHEN c_indexes%NOTFOUND;
    l_cmd := dbms_lob.substr(l_clob);
    l_cmd := REPLACE(l_cmd,'"."PS','"."GT');
    psftapi.message_log('DDL (len='||LENGTH(l_cmd)||'):'||l_cmd,0,TRUE);
    EXECUTE IMMEDIATE l_cmd;    
   END LOOP;
   CLOSE c_indexes;

   l_rename2  := 'ALTER TABLE '||l_gt_table_name||' RENAME TO '||l_ps_table_name;
  END IF;

 END IF;


 --need to change to temp table to permanent
 IF l_gt_control = 'N' AND l_ps_temporary = 'Y' THEN
  --dbms_output.put_line('Change Temporary to Permanent');

  IF l_gt_found THEN
   l_message := 'Cannot move '||l_ps_table_name||' to '||l_gt_table_name||', because it already exists';
   l_errno := -20005;
   RAISE e_error_message;
  ELSE
   l_rename1 := 'ALTER TABLE '||l_ps_table_name||' RENAME TO '||l_gt_table_name;
   l_recover := 'ALTER TABLE '||l_gt_table_name||' RENAME TO '||l_ps_table_name;
   l_truncate := 'TRUNCATE TABLE '||l_ps_table_name;
  END IF;

  IF l_xx_found THEN
   IF l_xx_temporary = 'Y' THEN
    l_message := 'Table '||l_xx_table_name||' should not be a Global Temporary Table.';
    l_errno := -20006;
    RAISE e_error_message;
   ELSE
    l_rename2 := 'ALTER TABLE '||l_xx_table_name||' RENAME TO '||l_ps_table_name;
   END IF;
  ELSE
   l_message := 'Cannot find backup of permanent table: '||l_xx_table_name||'.';
   l_errno := -20007;
   RAISE e_error_message;
  END IF;

 END IF;

 --and now we do the alters
 IF l_rename1 IS NOT NULL THEN
  psftapi.message_log('DDL:'||l_rename1,0,TRUE);
  EXECUTE IMMEDIATE l_rename1;
 END IF;

 IF l_rename2 IS NOT NULL THEN
 BEGIN
  psftapi.message_log('DDL:'||l_rename2,0,TRUE);
  EXECUTE IMMEDIATE l_rename2;
 EXCEPTION
  WHEN OTHERS THEN
   psftapi.message_log('DDL:'||l_recover,10,TRUE);
   EXECUTE IMMEDIATE l_recover;
   RAISE;
 END;
 END IF;

 IF l_truncate IS NOT NULL THEN
  --and now we do the truncate
  psftapi.message_log('DDL:'||l_truncate,0,TRUE);
  EXECUTE IMMEDIATE l_truncate;
 END IF;

EXCEPTION
 WHEN e_error_message THEN
  psftapi.message_log(l_message,ABS(l_errno),TRUE); --write message to PSFT message log
  RAISE_APPLICATION_ERROR(l_errno,'Trigger GFC_TEMP_TABLE_TYPE: '||l_message); 

 WHEN e_warning_message THEN
  psftapi.message_log(l_message,ABS(l_errno),TRUE); --write message to PSFT message log, but no error

 WHEN e_no_error THEN 
  NULL; -- terminate without any error

 WHEN OTHERS THEN
  dbms_output.put_line('Unhandled error on recname:'||:new.recname||:new.curtempinstance);
  RAISE;
END;
/
show errors
pause


/*----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

execute psftapi.set_prcsinstance(42,'Wibble');

DROP TABLE gt_tl_prof_list42;

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'WIBBLE',42,'PS','Wibble','WIBBLE',SYSDATE,'N',1,1)
/

----------------------------------------------------------------------------------------------------
DROP TABLE GT_WRK_SCH9_TAO1;


DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'WRK_SCH9_TAO',1,'PS','Wibble','WIBBLE',SYSDATE,'Y',1,1)
/
SELECT table_name, temporary
 from user_tables
where table_name like '%WRK_SCH9_TAO1'
/

select *
from user_tab_statistics
where table_name like '%WRK_SCH9_TAO1'
/
select *
from user_tab_statistics
where table_name like '%WRK_SCH9_TAO1'
/

pause




DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'WRK_SCH9_TAO',1,'PS','Wibble','WIBBLE',SYSDATE,'N',1,1)
/

SELECT table_name, temporary
 from user_tables
where table_name like '%WRK_SCH9_TAO1'
/
select *
from user_tab_statistics
where table_name like '%WRK_SCH9_TAO1'
/

pause

----------------------------------------------------------------------------------------------------
DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'TL_PROF_LIST',42,'PS','Wibble','WIBBLE',SYSDATE,'N',1,1)
/

pause

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'TL_PROF_LIST',42,'PS','Wibble','WIBBLE',SYSDATE,'Y',1,1)
/

pause

select table_name, temporary
 from user_tables
where table_name like '%TL_PROF_LIST%42'
/
select table_name, index_name, temporary
 from user_indexes
where table_name like '%TL_PROF_LIST%42'
/

----------------------------------------------------------------------------------------------------
DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'TL_EXCEPT_WRK',42,'PS','Wibble','WIBBLE',SYSDATE,'N',1,1)
/

pause

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
VALUES (0,'TL_EXCEPT_WRK',42,'PS','Wibble','WIBBLE',SYSDATE,'Y',1,1)
/

pause
----------------------------------------------------------------------------------------------------
DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
SELECT 0, recname, 1, 'PS', 'Wibble','WIBBLE',SYSDATE,'Y',1,1
FROM pstemptblcntvw a, user_tables b
WHERE temptblinstances > 0
AND b.table_name = 'PS_'||a.recname||'1'
/

pause


DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
SELECT 0, recname, 1, 'PS', 'Wibble','WIBBLE',SYSDATE,'N',1,1
FROM pstemptblcntvw a, user_tables b
WHERE temptblinstances > 0
AND b.table_name = 'PS_'||a.recname||'1'
and Not recname IN(select recname from gfc_temp_tables)
and temporary = 'Y'
/

pause


----------------------------------------------------------------------------------------------------

SELECT table_name, temporary
 from user_tables
where table_name like '%TL_EXCEPT_WRK%42'
/
select table_name, index_name, temporary
 from user_indexes
where table_name like '%TL_EXCEPT_WRK%42'
/

----------------------------------------------------------------------------------------------------
--Lock stats on all temp tables
----------------------------------------------------------------------------------------------------

BEGIN
 FOR i IN (SELECT table_name FROM user_tables WHERE temporary = 'Y' AND table_name like 'GT%') LOOP
  dbms_stats.lock_table_stats(user,i.table_name);
 END LOOP;
END;
/

----------------------------------------------------------------------------------------------------
--this SQL switches all tables back to normal
----------------------------------------------------------------------------------------------------

DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/
INSERT INTO ps_aetemptblmgr
(process_instance, recname, curtempinstance, oprid, run_cntl_id, ae_applid, run_dttm, ae_disable_restart, ae_dedicated, ae_truncated)
SELECT 0, recname, instance, 'PS', 'Wibble','WIBBLE',SYSDATE,'N',1,1
  FROM	(
	SELECT	recname, r.sqltablename, x.instance
	FROM	psrecdefn r
	,	(
		SELECT  rownum instance
		FROM	psrecdefn 
		WHERE 	rownum <= 99
		) x
	WHERE	r.rectype = 7
	) r
	INNER JOIN user_tables ps
		on ps.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||r.instance
		AND ps.temporary = 'Y'
	INNER JOIN user_tables xx
		on xx.table_name = 'XX_'||r.recname||r.instance
		AND xx.temporary = 'N'
/
DELETE FROM ps_aetemptblmgr
WHERE process_instance = 0
/

----------------------------------------------------------------------------------------------------
--Generate SQL to drop all GTTs
----------------------------------------------------------------------------------------------------

SELECT 	'DROP TABLE '||gt.table_name||' PURGE;'
FROM	user_tables gt
WHERE	temporary = 'Y'
and	table_name like 'GT%';


----------------------------------------------------------------------------------------------------*/

column recname format a15
column ps_table_name format a20
column xx_table_name format a20
column gt_table_name format a20
column ps_temp heading 'PS|TMP'
column gt_temp heading 'GT|TMP'
column xx_temp heading 'XX|TMP'
break on recname 
SELECT 	r.recname, r.instance
, 	ps.table_name ps_table_name, ps.temporary ps_temp
, 	gt.table_name gt_table_name, gt.temporary gt_temp
, 	xx.table_name xx_table_name, xx.temporary xx_temp
FROM	(
	SELECT	recname, r.sqltablename, x.instance
	FROM	psrecdefn r
	,	(
		SELECT  rownum instance
		FROM	psrecdefn 
		WHERE 	rownum <= 99
		) x
	WHERE	r.rectype = 7
--	AND	r.recname = 'TL_PROF_LIST'
	) r
	LEFT OUTER JOIN user_tables ps
		on ps.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||r.instance
	LEFT OUTER JOIN user_tables gt
		on gt.table_name = 'GT_'||r.recname||r.instance
	LEFT OUTER JOIN user_tables xx
		on xx.table_name = 'XX_'||r.recname||r.instance
WHERE	1=1
AND	(	gt.table_name IS NOT NULL
	OR	XX.table_name IS NOT NULL
	OR	xx.temporary = 'Y'
	)
ORDER BY 1,2
/


----------------------------------------------------------------------------------------------------*/

--alter TRIGGER GFC_TEMP_TABLE_TYPE disable;