Korn Script to connect and query oracle database

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Korn Script to connect and query oracle database
# 1  
Old 01-03-2012
Question Korn Script to connect and query oracle database

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

# ============================================================================
# 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`
# ---------------------------------------------------------------
# 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
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')
if [ "$?" -ne "0" ]
echo "11 Sqlplus returned a non standard return code of $?"
echo "$SQL"
echo "Aborting..."
exit 11
# ---------------------------------------------------------------
# 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.
## Start sqlplus in a coprocess.
sqlplus -s / |&
#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
if [[ "$output" == "$EOF" ]]; then
return 0
## 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
# ============================================================================
# ============================================================================
# ============================================================================
# ============================================================================
# Get the value of address RED parameters used.
# 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.
# Assign the output file to completion date and time: $completed.
completed=`head -1 $logfile`
# Send general email alert to support.
if [ "$supportto" != "X" ]
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" ]
echo "13 Mailx a non standard return code of $?"
echo "Aborting..."
exit 13
# Remove temporary sqlplus log file.
rm -f $logfile
# ============================================================================
# ============================================================================

Any pointers? Syntax right?

Cheers, Jack
# 2  
Old 01-03-2012
First impressions (without detailed look).
Neither of these variables are set anywhere in the script. This could cause your login error.

Should be two lines:
# 3  
Old 01-04-2012
Just pass the SQL credentials as below,

SQL=$(sqlplus -s $DSS_USER/$DSS_PWD <<EOF | grep -v "completed" | grep -v "^$" > $logfile

Also end should be,


Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Connect to Oracle database

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)
Discussion started by: refi123
2 Replies

2. UNIX and Linux Applications

Identify a specific environment Oracle variable to connect a remote Oracle database ?

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)
Discussion started by: alexcol
2 Replies

3. Shell Programming and Scripting

How to connect to Oracle database using shell script?

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)
Discussion started by: pmotewar
3 Replies

4. Shell Programming and Scripting

Shell Script to connect to the oracle database

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)
Discussion started by: bshivali
8 Replies

5. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

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)
Discussion started by: vel4ever
9 Replies

6. Programming

Oracle Database Query

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)
Discussion started by: SkySmart
3 Replies

7. Shell Programming and Scripting

Different way to connect to database ans execute sql query

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)
Discussion started by: gauravgarg
6 Replies

8. Shell Programming and Scripting

How to connect with oracle database using unix...

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)
Discussion started by: vijays3
6 Replies

9. Shell Programming and Scripting

How to COnnect to Oracle database from UNIX Box

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)
Discussion started by: angelarosh
12 Replies

10. Shell Programming and Scripting

Connect to sybase database using Korn shell script

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)
Discussion started by: bhgopi
7 Replies
Login or Register to Ask a Question

Featured Tech Videos