#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Creates private synonyms in the user's Oracle account so that
#         the tables in the DPS database account can be accessed by the
#         table name.
#
#INVOCATION METHOD: ins_createsyn db_owner/pw 
#
#
#DEVELOPMENT HISTORY:
#
#       Author          Change-ID  Release     Date    Description of Change
#       ------          ---------  -------     ----    ---------------------
#    S. Azama                      Build 2    10/3/93  Initial release
#    P. Matthews                   Build 2     1/4/94  Changed host name from 
#                                                       variable 'hostname' to an
#							input parameter
#    P. Matthews                   B3R2      07/07/94  Removed hardwired synonym
#                                                       names and enhanced flexiblity
#    M.Rauschenberger              B4R3      07/07/94  Added selects for PDP
#    P. Matthews                   B4R2.1    11/16/94  Added condition on table name
#    P. Matthews                   B4R2.1    01/13/95  Modified condition on table
#                                                       name to exclude gen tables
#    I. Horowitz                   ACE R1.0  10/18/95  Removed test for host type
#    I. Horowitz		   ACE R1.0  02/22/96  Removed links from selects
#    I. Horowitz	ACE0615	   ACE R2.0  07/16/96  modified to incorporate db
#							security and to exclude
#							QAWS tables
#    I. Horowitz	ACE0615	   ACE R2.0  08/02/96  eliminated exit stmt from 
#							tmp_synonyms.sql
#    R. Wiechert        ISTP       R1.2      04/22/98  Relocate temporary files to /tmp
#
#
#NOTES: This script is run by each Oracle user who needs to access the DPS
#       database.
#*********************************************************************************
if [ $# -lt 1 ]
then
echo '**********************************************************************'
echo 'Error, you must enter an Oracle DbOwnerId/Password '
echo '**********************************************************************'
exit
fi
echo ""
echo "****************************************************"
echo "Creating synonyms that may be granted to users ..."
echo "****************************************************"
echo ""

tmp_syn_file=/tmp/oracle_synonyms_$$.sql
rm -f $tmp_syn_file

owneracct=`echo $1 | awk -F/ '{ print $1 }'`

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_syn_file

select 'create synonym ' || RTRIM(TABLE_NAME) || ' for '
|| RTRIM(USER) || '.' || LTRIM(TABLE_NAME) || ';' 
from USER_TABLES
where (table_name like 'DB_%'
and   table_name != 'DB_PRODUCTHOST'
and   table_name != 'DB_IDFSETUP'
and   table_name != 'DB_ORACLELOGIN'
and   table_name != 'DB_QORACLELOGIN')
or    table_name like 'PDP_%'
or    table_name like 'PSC_%';

select 'create synonym ' || RTRIM(SEQUENCE_NAME) || ' for '
|| RTRIM(USER) || '.' || LTRIM(SEQUENCE_NAME) || ';' 
from USER_SEQUENCES;

select 'create synonym PICS_dual for sys.dual;' from dual;

spool off

rem 
rem -------------> set up secure passwords
rem
truncate table db_OracleLogin;
insert into db_OracleLogin
values ('PICS', '${owneracct}_PICSPROCESS/picsprocess');
insert into db_OracleLogin
values ('PDP', '${owneracct}_PDPSPROCESS/pdpsprocess');
 
rem
rem -------------> create synonyms for users
rem
connect ${owneracct}_PICSPROCESS/picsprocess;
  start $tmp_syn_file;
  create synonym db_IDFSetup for ${owneracct}.db_IDFSetup;
connect ${owneracct}_PDPSPROCESS/pdpsprocess;
  start $tmp_syn_file;
  create synonym db_ProductHost for ${owneracct}.db_ProductHost;
connect ${owneracct}_USER/analyst;
  start $tmp_syn_file;

ENDORACLE

rm -f $tmp_syn_file
echo ""
echo "*******************************************************"
echo 'Synonyms have been created ...'
echo "*******************************************************"
echo ""
exit
