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
Code:
#!/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=$1jobseq=$2jobname=$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_PWDset sqlprompt ""set heading offset pagesize 0set linesize 256set trimspool onset echo offset feed offSELECT 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" ]thenecho "11 Sqlplus returned a non standard return code of $?"echo "$SQL"echo "Aborting..."exit 11fi}# ---------------------------------------------------------------# 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=terminationtrap '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 outputdoif [[ "$output" == "$EOF" ]]; thensend_emailreturn 0else## eval forces the shell to evaluate the line twice. First, replacing## "$output" with "COUNT1=99999", then again which creates and sets## a variable.echo $outputfidone}# ============================================================================# ============================================================================# 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" ]thenecho $(get_pending) | mailx -s "There is currently one of more jobs with PENDING STATUS in $environment $ORACLE_SID" -r "$retaddr" "$supportto"if [ "$?" -ne "0" ]thenecho "13 Mailx a non standard return code of $?"echo "Aborting..."exit 13fifi# Remove temporary sqlplus log file.rm -f $logfile# ============================================================================# END# ============================================================================exit SUCCESS
Hi,
Can anyone please give me a script or let me know how to connect to a sybase database and execute a query using Korn shell scripts.Am new to Unix but i need to do this ASAP.
Please help.
Thanks,
Gops (7 Replies)
I am trying to connect to Oracle Databse from UNIX box using sqlplus command. But i get sqplus not found message.
$ sqlplus
sqlplus: not found
I have searched in forums and all are suggesting to set environment varaibles...can somebody tell me what variables to set and how to set.and the... (12 Replies)
Hi all
I am working in datawarehouse project and use DB2 database . shell scripting is written to connect with DB2 database in my application but i dont know how to connect with oracle databse or other databases.Is there any command in unix to connect any of these datbases? (6 Replies)
Hi Friends,
I am using AIX version and sqlplus to connect to database while writing a shell script.
I have implemented three ways to connect to database :
1)
sqlplus -s <USERNAME>/<PASSWORD> <<!
@<SQL FILE TO EXECUTE>
exit
!
2)
sqlplus -s <USERNAME>/<PASSWORD> <<!
-----sql statemenets... (6 Replies)
How can i modify the below to search for the things i'm looking for during a certain time frame?
select Node, NodeAlias, Summary, Tally, AlertKey, AlertGroup, Manager, Agent from mrtg_alerts where LastOccurrence > '5-Dec-2010' order by Manager desc;
In this particular case, this query is... (3 Replies)
I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this?
Is there a way to have a persistent connection to oracle database... (9 Replies)
Hi Unix Gurus,
I have a requirement to write a script, Oracle DB gets referesh every 6hrs, hence we need write a script to connect to the database , if it connects it should generate a file to proceed the next job application or when an error when connectiong to DB it should not generate any... (8 Replies)
Hi All,
I am newbie to unix shell scripting. I want to connect to oracle database using shell script. I know how to connect DB using user name and password but my question is if my password is having '@' sign then how to connect.
I have tried like below,
cnt=`sqlplus -s /nolog << EOFSQL
... (3 Replies)
Good evening
I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus.
I tried to find out the user/passwd and service name by env variable and all Ive got is this:
ORACLE_SID_REPCOL=SCL_REPCOL
ORACLE_SID=xmeta
ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
could someone please help me in shell scripting. i want to connect to oracle database which is on remote sever.
requirement:
1 want to check files in source directory if file exist then a execute a corresponding batch jobs on unix sever and fetch data from oracle database which is on remote... (2 Replies)