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:

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

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?

Question 1: So I guess when the connection fails, there will be some error messages which will make

if [ $? -eq 0 ]

evaluate to false, correct? But if there are error messages or any output from

grep -q "Connected to:" > /dev/null

it will already be directed to /dev/null so how will

if [ $? -eq 0 ]

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

Q2: for

echo "exit" | sqlplus ....

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

Here is the function 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.

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.

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

if [ $? -eq 0 ]

tests against the true or false values of grep.

So the 0 or 1 doesn't get lost when we have

> /dev/null

, correct?

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.

1 Like

Very clear thank you so much.
Q2: for

echo "exit" | sqlplus ....

Where does the "exit" go or how is it being used?

It gets printed to standard output, which by default is your terminal, but pipes | and redirects > send it somewhere else.

$ echo "something"

something

$

Pipes grab the output of the command before them and send them into the standard input of the command after them. Standard input, by default, is the terminal too.

"default" is just "a copy of whatever file descriptors your terminal happens to have open", incidentally.

So you're typing "exit" into sqlplus, then scanning whatever it prints back out for "Connected To:" to see if it worked.

1 Like

Thank you so much. I wish there is an online tutorial for running SQL scripts from Bash. I found some but the explanation is not clear or detailed.

Let me know if you know any.

Regards,

All your questions are generic Bourne shell questions so far, the command reads text in and prints text out, no special SQL knowledge required. So you're just looking for the wrong kind of tutorial really.

The redirect to /dev/null for standard output is not needed if you use the -q flag on grep because that means no output anyway.

Have you considered a database that is up & running but locked up due to the redo area being full? You might not get control back?

Additionally, you will be able to see (albeit briefly) the credentials by running a simple ps command at the right time on another session. You might be better with:-

sqlplus $userid@$target_db <<-EOSQL
$password
EOSQL

Do you not have a tnsnames.ora file to describe how to get to your database instances? It can be put in a shared area and makes adjusting it for multiple users far easier. It might be worth a read.

Robin

1 Like