#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Drops ALL existing db objects (tables,synonyms & sequences) for a user
#
#INVOCATION METHOD: ins_remobjects user/pw log_file
#
#
#DEVELOPMENT HISTORY:
#
#       Author          Change-ID   Release   Date     Description of Change
#       ------          ---------   -------   ----     ---------------------
#    M. Rauschenberger  B4R3    	      08/25/94 Initial release
#    I. Horowitz        ACE0615     ACE R2.0  06/30/96 Added -s option to sqlplus cmd line
#    R. Wiechert        ISTP        R1.2      04/22/98 Relocate temporary files to /tmp
#
#
#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/oracle_remove_$$.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 table '          || RTRIM(TABLE_NAME)    || ';' from USER_TABLES;
select 'drop synonym '        || RTRIM(SYNONYM_NAME)  || ';' from USER_SYNONYMS;
select 'drop sequence '       || RTRIM(SEQUENCE_NAME) || ';' from USER_SEQUENCES;
select 'drop public synonym ' || RTRIM(SYNONYM_NAME)  || ';' from all_synonyms 
where  TABLE_OWNER = USER;

spool off
set term on
set echo off
ENDORACLE

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

rm -f $tmp_file
echo ""
echo "*************************************************************"
echo "Existing tables, synonyms and sequences have been removed ..."
echo "*************************************************************"
echo ""
