I've been sent the following script to finish. It's supposed to connect to an oracle database, query it, and send an email if the query result value is one or more. Currently it isn't connecting properly, just giving the following error:
ERROR: ORA-01017: invalid username/password; logon denied SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where <logon> ::= <username>[/<password>][@<connect_identifier>] [edition=value] | / SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where <logon> ::= <username>[/<password>][@<connect_identifier>] [edition=value] | / SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
#!/bin/ksh
# ============================================================================
# Script Name : send_mail_message.sh
# Description : Send mail alerts.
# RDBMS : Oracle
# Author : Jason Laws, WhereScape
# Date Created : Version 1.0.0 31/08/2004
# Second Author : Jack Morgan, Invacare
# Date Modified : Version 1.1.0 15/12/2010
# =============================================================================
# Notes / History
# Send a mail message informing of a jobs completion status.
# =============================================================================
# Initialize variables.
filename=`echo $0 | awk -F"/" '{ print $NF }' | awk -F"." '{ print $1 }'`
logfile=`echo /tmp/$filename.$$.tmp`
status=$1
jobseq=$2
jobname=$3
# ---------------------------------------------------------------
# Function : get_parameters
# Purpose: Get the value of address RED parameters used.
# ---------------------------------------------------------------
function get_parameters {
SQL=`sqlplus -s <<EOF | grep -v "completed" | grep -v "^$" > $logfile
$DSS_USER/$DSS_PWD
set sqlprompt ""
set heading off
set pagesize 0
set linesize 256
set trimspool on
set echo off
set feed off
SELECT NVL(wsparameterread('DWAlertSender'),'X')
, NVL(wsparameterread('red_support_address'),'X')
, NVL(wsparameterread('red_support_phone_number'),'X')
, NVL(wsparameterread('DWEnvironment'),'X')
FROM DUAL;
exit;
EOF`
if [ "$?" -ne "0" ]
then
echo "11 Sqlplus returned a non standard return code of $?"
echo "$SQL"
echo "Aborting..."
exit 11
fi
}
# ---------------------------------------------------------------
# Function : get_pending
# Purpose: Get the time the job completed or failed.
# ---------------------------------------------------------------
function get_pending {
output="" ## Output from SQL/Plus goes here.
set -f output ## Don't do filename expansion on this variable. For when an error
## from SQL/Plus contains a splat (*).
integer rc ## Holds a return code.
typeset -r ERRFILE=$0.err ## Define an error file.
typeset -r EOF="DONE" ## Text used to indicate the end of SQL/Plus output.
## Create the error file or zero it out if it already exists.
> $ERRFILE
## Start sqlplus in a coprocess.
sqlplus -s / |&
#Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}]
#where <logon> ::= <username>[/<password>][@<connect_identifier>] [edition=value] | /
print -p "CONNECT username/password as sysdba;"
## Exit SQL/Plus if any of the following signals are received:
## 0=normal exit, 2=interrupt, 3=quit, 9=kill, 15=termination
trap 'print -p "exit"' 0 2 3 9 15
## Send commands to SQL/Plus.
print -p "set heading off;"
print -p "set feedback off;"
print -p "set pagesize 0;"
print -p "set linesize 500;"
##
## Send a query to SQL/Plus. It is formatted so we can set a shell variable.
##
print -p "select count(1) from WS_WRK_JOB_CTRL WHERE WJC_STATUS='H';"
print -p "prompt $EOF" ## This is an indicator that we reached the end
## of selected data. When we read a DONE from
## the coprocess we know we have no more data.
## Read the output from the coprocess a line at a time. When DONE
## is read, that indicates the end of output.
while read -p output
do
if [[ "$output" == "$EOF" ]]; then
send_email
return 0
else
## eval forces the shell to evaluate the line twice. First, replacing
## "$output" with "COUNT1=99999", then again which creates and sets
## a variable.
echo $output
fi
done
}
# ============================================================================
# ============================================================================
# MAIN
# ============================================================================
# ============================================================================
# Get the value of address RED parameters used.
get_parameters
# Assign the parameter values to variables.
retaddr=`head -1 $logfile`
supportto=`head -2 $logfile | tail -1`
supportno=`head -3 $logfile | tail -1`
environment=`head -4 $logfile | tail -1`
# Get the job completion date and time.
get_pending
# Assign the output file to completion date and time: $completed.
completed=`head -1 $logfile`
# Send general email alert to support.
if [ "$supportto" != "X" ]
then
echo $(get_pending) | mailx -s "There is currently one of more jobs with PENDING STATUS in $environment $ORACLE_SID" -r "$retaddr" "$supportto"
if [ "$?" -ne "0" ]
then
echo "13 Mailx a non standard return code of $?"
echo "Aborting..."
exit 13
fi
fi
# Remove temporary sqlplus log file.
rm -f $logfile
# ============================================================================
# END
# ============================================================================
exit SUCCESS
Any pointers? Syntax right?
Cheers, Jack