Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Sqlplus code format

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Sqlplus code format
# 1  
Old 01-25-2017
Reference Sqlplus code format
Hi,

I have some questions about sqlplus running from bash. I am still new and learning. I have the code shown below:

Code:
echo "exit" | sqlplus "${DB_UserName}/${DB_Password}@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${DB_HostName})(PORT=${DB_Port})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=${DB_SID})))" | grep -q "Connected to:" > /dev/null
if [ $? -eq 0 ]
then
DB_STATUS="UP"

I don't get what the pipe to grep -p is doing and the
Code:
if [ $? -eq 0 ]
then
DB_STATUS="UP"

part. I understand that it's trying to search for "Connected to:" and then send the output to /dev/null. Then if the output of the previous command is 0, the DB_STATUS is set to UP. I just don't understand the logic.

Thank you for your time.
# 2  
Old 01-25-2017
Quote:
Originally Posted by mohca2020
I don't get what the pipe to grep -p is doing and the
Code:
if [ $? -eq 0 ]
then
DB_STATUS="UP"

part. I understand that it's trying to search for "Connected to:" and then send the output to /dev/null. Then if the output of the previous command is 0, the DB_STATUS is set to UP. I just don't understand the logic.

Thank you for your time.
The logic is, If the outputted text contains "Connected do", set DB_STATUS=up. There's really no more to it than that. Presumably sqlplus won't print that when it's not connected, and DB_STATUS will be left at whatever it was before, presumably nothing.

Why are they setting that variable? I have no idea. What uses that variable?
# 3  
Old 01-25-2017
Question 1: So I guess when the connection fails, there will be some error messages which will make
Code:
if [ $? -eq 0 ]

evaluate to false, correct? But if there are error messages or any output from
Code:
grep -q "Connected to:" > /dev/null

it will already be directed to /dev/null so how will
Code:
if [ $? -eq 0 ]

ever evaluate to false if the output is always directed to /dev/null?

Q2: for
Code:
echo "exit" | sqlplus ....

where does the "exit" go or how is it used?

Here is the function code:

Code:
db_statuscheck() {
echo "`date` :Checking DB connectivity...";
echo "`date` :Trying to connect "${DB_UserName}"/"${DB_Password}"@"${DB_SID}" ..."
# Quiet; do not write anything to standard output. Exit immediately with zero status if any match is found, even if an error was detected.
echo "exit" | sqlplus "${DB_UserName}/${DB_Password}@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${DB_HostName})(PORT=${DB_Port})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=${DB_SID})))" | grep -q "Connected to:" > /dev/null
if [ $? -eq 0 ]
then
DB_STATUS="UP"
export DB_STATUS
echo "`date` :Status: ${DB_STATUS}. Able to Connect..."
else
DB_STATUS="DOWN"
export DB_STATUS
echo "`date` :Status: DOWN . Not able to Connect."
echo "`date` :Not able to connect to database with Username: "${DB_UserName}" Password: "${DB_Password}" DB HostName: "${DB_HostName}" DB Port: "${DB_Port}" SID: "${DB_SID}"."
echo "`date` :Exiting Script Run..."
exit
fi
}

Again, thank you for your time.
# 4  
Old 01-25-2017
Quote:
Originally Posted by mohca2020
Question 1: So I guess when the connection fails, there will be some error messages which will make
Code:
if [ $? -eq 0 ]

evaluate to false, correct?
No, grep does that. Being the last command run, it's what will set $?. grep returns 0 when it finds the text it was looking for and 1 when it doesn't.
# 5  
Old 01-25-2017
Oooh, okay I get it. So if the string "Connected to: " is found, grep will give 0 for true and 1 for false (which means not found). Then
Code:
if [ $? -eq 0 ]

tests against the true or false values of grep.

So the 0 or 1 doesn't get lost when we have
Code:
> /dev/null

, correct?
# 6  
Old 01-25-2017
Nope. > /dev/null is not a command, just a redirection, so doesn't affect $?

Well, usually doesn't. If the file can't be opened, the commands won't be run at all, and exit status ( $? ) will be 255 or something like that.

$? is a variable, not a stream, nothing captures it. All that matters is what was run last.
This User Gave Thanks to Corona688 For This Post:
mohca2020
# 7  
Old 01-25-2017
Very clear thank you so much.
Q2: for
Code:
echo "exit" | sqlplus ....

Where does the "exit" go or how is it being used?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

2. UNIX and Linux Applications

Please help: Oracle gqsql or sqlplus output format like mysql

On psql select titolo,lingua from titolo where titolo ~* 'brivid'; titolo | lingua ------- + ------ Brivido | 1 On Sqlplus/gqsql SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%'; TITOLO... (6 Replies)
Discussion started by: Linusolaradm1
6 Replies

3. Shell Programming and Scripting

Shell Script passing parameters to sqlplus code

Hello All, I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible? Thanks (4 Replies)
Discussion started by: compprog11
4 Replies

4. Shell Programming and Scripting

perl code-sequence of json format

Hi All , Below is the perl code. from below code want to confirm one thing that wahtever the sequence of data we are passing through json format which contains 3 tuples of different sequences Eg: ParentID,SystemID,SendingTime,Time,ClientLocation,ClientID, ... (1 Reply)
Discussion started by: aish11
1 Replies

5. Shell Programming and Scripting

need code for date which is in yyyy-mm-dd format

Hi, I am having one log files. contains som data according to date. And it is going to append .Eg:abc.log contains below data 2011-10-19 abjhgj 2011-10-19 gjhgjgj 2011-10-20 hhhjh 2011-10-20 hhhhjj 2011-10-21 gg . . . 2011-11-24 yyy from log files i want catch only... (2 Replies)
Discussion started by: aish11
2 Replies

6. Programming

Basic perl code- Date format

Hi friends, Please see the below code carefully. ======================================================= # Get batch date and Ord range open OR,$ARGV; while (<OR>) { # find the batch date next if length $_ < 3; # BLANK LINE # last if $. > 120; # sample should be good enough... (2 Replies)
Discussion started by: pspriyanka
2 Replies

7. Shell Programming and Scripting

Format the output from sqlplus while writing to log file.

Hi I have developed bash script to connect to database and execute .sql files. I am logging some statements in to log file using echo. While logging I am adding the date in front of the log statements which makes sense. I am unable to add date in front of output from the sqlplus and sqlldr,... (8 Replies)
Discussion started by: murtymvvs
8 Replies

8. Shell Programming and Scripting

code to FTP the spool file from Sqlplus to the unix server.

I have a sqlplus report and inside that report I have the following piece of code to mail the report output to the email id. My requirement is, instead of emailing the output I want to FTP that script to a different unix server (say ip as 10.10.1.1). How to modify this code to FTP the spool file... (0 Replies)
Discussion started by: vprevin
0 Replies

9. UNIX for Dummies Questions & Answers

File Format issue: Output of sqlplus

Hi, I am using a query like below in my shell script : { { echo "set echo off" echo "set head off" echo "whenever sqlerror exit -1; select NUMBER ||','|| FNAME ||','|| LOC ||','|| ... (2 Replies)
Discussion started by: deepakgang
2 Replies

10. Programming

how i prepare a c++ code(c code) for implementing my own protocol format

helo my protocol format is given below { destno,mode,no.of packet,pktsize,,pktno,textsize,CRC} description:- { is starting flag destno - 4bytes mode - 1 byte no.of pkt - 4byes pktsize - 6 bytes ... (1 Reply)
Discussion started by: amitpansuria
1 Replies
Login or Register to Ask a Question