#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Drops ALL existing QAWS db objects (tables,synonyms & sequences) for a user
#
#INVOCATION METHOD: ins_remobjects_QAWS user/pw log_file
#
#
#DEVELOPMENT HISTORY:
#
#       Author          Change-ID   Release   Date     Description of Change
#       ------          ---------   -------   ----     ---------------------
#    I. Horowitz        ACE0615     ACE R2.0  06/30/96 Added for merge of QAWS tables 
#							with DPS DB
#
#
#NOTES: This script is run by each Oracle Database account owner.
#*********************************************************************************
if test $# -ne 2
then
   echo '************************************************************'
   echo 'Error, you must enter an Oracle UserID/Password and log name'
   echo '************************************************************'
   exit
fi

echo ""
echo "****************************************************"
echo "Removing existing tables, synonyms and sequences ... "
echo "****************************************************"
echo ""

tmp_file=tmp_qaw_remobjects.sql
rm -f $tmp_file

sqlplus  -s /nolog <<ENDORACLE >> /dev/null
connect $1
clear breaks
set showmode off
set term off
set echo off
set heading off
set pagesize 0
set linesize 100
set feedback off

spool $tmp_file

select 'drop synonym '        || RTRIM(SYNONYM_NAME)  || ';' from USER_SYNONYMS
where  TABLE_NAME in (
   select TABLE_NAME from USER_TABLES
   where  tablespace_name = 'QAWS_DATA'
);

select 'drop sequence '       || RTRIM(SEQUENCE_NAME) || ';' from USER_SEQUENCES
where  SEQUENCE_NAME like 'Q%';

select 'drop public synonym ' || RTRIM(SYNONYM_NAME)  || ';' from all_synonyms 
where TABLE_OWNER = USER
and   TABLE_NAME in (
   select TABLE_NAME from USER_TABLES
   where  tablespace_name = 'QAWS_DATA'
);

select 'drop table '          || RTRIM(TABLE_NAME)    || ';' from USER_TABLES
where  tablespace_name = 'QAWS_DATA';

spool off
set term on
set echo off
ENDORACLE

sqlplus /nolog <<ENDORACLE >> $2
connect $1
start $tmp_file 
quit
ENDORACLE

echo ""
echo "******************************************************************"
echo "Existing QAWS tables, synonyms and sequences have been removed ..."
echo "******************************************************************"
echo ""
