#################################################################################################
# .profile_sub_dbas 		(c) Rob Stubbings 4th October 1996	Version 1.0		#
#################################################################################################
# 951004 RDS Genesis
# 970405 RHR Revised name


#===========================================================================================
s_sort_trunc_string()		# (c) Rob stubbings remove multiple occurances in a list 
#===========================================================================================
{
	echo $1 | $ORACLE_BASE/bin/new_tr ' ' '\n' | sort -u | $ORACLE_BASE/bin/new_tr '\n' ' '
}

#===========================================================================================
s_get_host_list()		# (c) Rob stubbings tell all cape severs
#===========================================================================================
{
	_connect=""
	_lines=`grep -v dummy_db $TNSNAMES`
	for _line_no in $_lines
	do
		_host=`echo $_line_no |cut -d= -f6-6 | cut -d')' -f1-1`
		_host=`s_truncate $_host`
		_connect="$_host $_connect"
	done
	echo `s_sort_trunc_string "$_connect"`
}

#===========================================================================================
s_sid_list2()			# (c) Rob Stubbings  Create a list of db sids for this host
#===========================================================================================
# Arg_1 = [HOSTNAME]
{
	_connect=""
	_lines=`grep ${1:-$THISHOST} $TNSNAMES| grep -v dummy_db`
	for _line_no in $_lines
	do
		_tns=`echo $_line_no | cut -f1-1 -d=`
		_host=`echo $_line_no |cut -d= -f6-6 | cut -d')' -f1-1`
		_host=`s_truncate $_host`
		if [ "${1:-$THISHOST}" = "$_host" ]
		then
			_connect="$_tns $_connect"
		fi
	done
	echo $_connect
}

#===========================================================================================
s_connect_list2()		# (c) Rob Stubbings Create a list of global connect strings
#===========================================================================================
# Arg_1 = [HOSTNAME]
{
	_connect=""
	if [ "$1" = "" ]
	then
		_lines=`grep -v dummy_db $TNSNAMES`
	else
		_lines=`cat $TNSNAMES`
	fi
	for _line_no in $_lines
	do
		_tns=`echo $_line_no| cut -f1-1 -d=`
		_connect="@$_tns $_connect"
	done
	echo $_connect
}

#===========================================================================================
s_connect_list_not()			# (c) Richard Reepe Creates NOT list of global connect 
strings
#===========================================================================================
# Arg_@ = NOT_TYPEs
{
	_connect=""
	_types=`s_initials $@`
	_types=`s_lower_case $_types`
	for _line_no in `grep -v dummy_db $TNSNAMES`
	do
		_tns=`echo $_line_no| cut -f1-1 -d=`
		_tns_type=`echo $_tns | cut -c6`
		if [ `echo "$_types" | grep -c $_tns_type` -eq 0 ]
		then
			_connect="@$_tns $_connect"
		fi
	done
	echo $_connect
}

#===========================================================================================
s_validate_db()		# (c) Rob Stubbings  Returns number of passed DB's matched
#===========================================================================================
# Arg_1 = DATABASE
# Arg_2 = [dummy] (any old crap)
{
	_database_string=`echo "${1:-ZZZZZZZZ}"`
	if [ "$2" = "" ]
	then
		cut -f1,9 -d= < $TNSNAMES | grep -i $_database_string | grep -cv "dummy_db"
	else
		cut -f1,9 -d= < $TNSNAMES | grep -ci $_database_string
	fi
}

#===========================================================================================
s_get_host()			# (c) Rob Stubbings  Returns hostname for given DB
#===========================================================================================
# Arg_1 = DATABASE
{
	if [ "${1:?s_get_host(Missing_DATABASE)}" = "" ]
	then
		exit
	fi
	_lines=`grep ${1} $TNSNAMES| grep -v dummy_db`
	for _line_no in $_lines
	do
		_tns=`echo $_line_no| cut -f1-1 -d=`
		_host=`echo $_line_no |cut -d= -f6-6 | cut -d')' -f1-1`
		if [ "$1" = "$_tns" ]
		then
			_host=`s_truncate $_host`
			echo $_host 
			break
		fi
	done
}

#===========================================================================================
s_select_db2()			# (c) R. H. Reepe. Returns a list of databases of Type
#===========================================================================================
# Arg_1 = DATABASE_TYPE
# Arg_2 = DATABASE_SERVER {Default: THISHOST}
{
	if [ "${1:?s_select_db2(Missing_DATABASE_TYPE)}" = "" ]
	then
		exit
	fi
	_database_type=""
	_type=`s_lower_case ${1} | cut -c1-1`
	_local_databases=`s_sid_list2 ${2:-$THISHOST}`
	for _database in $_local_databases
	do
		_db_type=0
		_db_type=`echo $_database | grep -c "_$_type"`
		if [ $_db_type -gt 0 ]
		then
			_database_type="$_database_type $_database"
		fi
	done
	echo $_database_type
}

#===========================================================================================
s_series_db2()			# (c) R. H. Reepe. Returns a list of databases of Type
#===========================================================================================
# Arg_1 = DATABASE_SERIES
# Arg_2 = DATABASE_SERVER {Default: THISHOST}
{
	if [ "${1:?s_series_db2(Missing_DATABASE_SERIES)}" = "" ]
	then
		exit
	fi
	_database_series=""
	_series=`s_lower_case $1 | cut -c1-1`
	_local_databases=`s_sid_list2 ${2:-$THISHOST}`
	for _database in $_local_databases
	do
		_db_series=0
		_db_series=`echo $_database | grep -c "0$_series"`
		if [ $_db_series -gt 0 ]
		then
			_database_series="$_database_series $_database"
		fi
	done
	echo $_database_series
}

#===========================================================================================
s_sqlplus()			# (c) R. H. Reepe. Runs the SQL statements and Logs result
#===========================================================================================
# Arg_1 = CODED_USER_NAME
# Arg_2 = DATABASE
# Arg_3 = SQL_STATEMENT_FILE
# Arg_4 = [RESULT_LOG]
{
	if [ "${3}" = "" ]
	then
		echo "SYNTAX: s_sqlplus CODED_USER_NAME DATABASE SQL_STATEMENT_FILE [RESULT_LOG]"
		exit
	elif [ ! -f ${3} ]
	then
		echo "s_sqlplus(SQL_STATEMENT_FILE: Does Not EXIST)"
		exit
	elif [ `grep -ci exit ${3}` = 0 ]
	then
		echo "s_sqlplus(SQL_STATEMENT_FILE: With No EXIT)"
		exit
	elif [ `s_validate_db $2` = 0 ]
	then
		echo "s_sqlplus(DATABASE: Invalid Name)"
		exit
	elif [ `echo ${1} | grep -ci coded` -eq 1 ]
	then
		echo "s_sqlplus(CODED_USER_NAME: Missing \$)"
		exit
	fi
	s_access_account ${1:-$system_coded} $0 $$_z
	. $account_string$$_z
	s_delete_account $$_z
	sqlplus -s ${account}@${2} @${3} > ${4:-/dev/null}
}