/*****************************************************************************
*
* Filename         - $Workfile:   trunc_temp.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Script to truncate all tables in temp schema.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
SET ECHO OFF FEEDBACK OFF

DECLARE
   CURSOR c_disable_fkeys IS
      SELECT 'ALTER TABLE ' || 'temp.' || table_name ||
             ' DISABLE CONSTRAINT ' || constraint_name AS ddl_text
        FROM user_constraints
       WHERE constraint_type = 'R';
   --
   CURSOR c_truncate_tables IS
      SELECT 'TRUNCATE TABLE ' || 'temp.' || table_name ||
             ' REUSE STORAGE' AS ddl_text
        FROM user_tables;
BEGIN
   --in case the script is accidentally run as live or arch
   IF USER != 'TEMP' THEN
      raise_application_error(-20001,
                              'User is ' || USER ||
                              '. This script should be run by TEMP only.');
   END IF;
   --As a sanity check, disable any foreign keys in temp schema.
   --(Foreign keys are not supposed to exist in temp schema)
   FOR r_disable_fkeys IN c_disable_fkeys LOOP
      EXECUTE IMMEDIATE r_disable_fkeys.ddl_text;
   END LOOP;
   --Now truncate all tables in temp schema
   FOR r_truncate_tables IN c_truncate_tables LOOP
      EXECUTE IMMEDIATE r_truncate_tables.ddl_text;
   END LOOP;
END;
/


exit

