Connecting to database in unix

We have provided below some scripts as quick reference for connecting to database in linux via sqlplus.

1. Script to connect and verify whether Oracle database is up – via sqlplus from Linux.

bash-3.2$ more connect_db.sh
#!/bin/bash
ORACLE_HOME=/app/a_oracle/product/11.1.0/client_1;export ORACLE_HOME
TNS_ADMIN=/home/myuser/tnsadmin;export TNS_ADMIN
PATH=$PATH:$ORACLE_HOME/bin;export PATH

echo exit | sqlplus -L $1/$2@$3 >/dev/null
if [ $? -eq 0 ]
then
   echo "DB Connectivity successful"
else
   echo "DB Connectivity failed"
fi
bash-3.2$

Explanation:
We need to set ORACLE_HOME, TNS_ADMIN and put ORACLE_HOME in PATH.
Then we call sqlplus with -L option and connect to db using the string “USERNAME/PASSWORD@DB_TNSENTRY”

-L	Attempts to log on just once, instead of
	reprompting on error.

>/dev/null: Will redirect the SQL*PLUS banner, prompts and all output to /dev/null so that those information will not get printed.
Then we check the exit status ($?) of the sqlplus command. If it is equal to 0, DB Connectivity is successful. Else, DB Connectivity is failed.
$? is a bash “special parameter” and the definition from man page is provided below.

$?	Expands to the exit status of the most recently
	executed foreground pipeline.

The above mentioned shell script can be executed as below.

bash-3.2$ ./connect_db.sh db_user db_pwd db_tns
DB Connectivity successful
bash-3.2$ ./connect_db.sh db_user invalid_pwd db_tns
DB Connectivity failed

2. Script to check Oracle database connectivity and to print ORA errors for failures – via sqlplus from Linux.

bash-3.2$ more connect_db.sh
#!/bin/bash
ORACLE_HOME=/app/a_oracle/product/11.1.0/client_1;export ORACLE_HOME
TNS_ADMIN=/home/myuser/tnsadmin;export TNS_ADMIN
PATH=$PATH:$ORACLE_HOME/bin;export PATH

echo exit | sqlplus -L -S $1/$2@$3 >output.txt
if [ $? -eq 0 ]
then
   echo "DB Connectivity successful"
else
   error_output=`sed -n '/ORA-/p' output.txt`
   echo "DB Connectivity failed ERROR: " ${error_output}
fi
bash-3.2$

Explanation:
Here we are using -S option of sqlplus to suppress the unwanted display.

-S	Sets silent mode which suppresses the display of
        the SQL*Plus banner, prompts, and echoing of
        commands. 

>output.txt: Here we are redirecting the output to output.txt.
If sqlplus could not connect to the database, it enters the else block.
error_output=`sed -n ‘/ORA-/p’ output.txt` : The sed command parses the output.txt and stores the line which contains ‘ORA-‘ into error_output. Then it is printed in the next line.

When this script is executed with invalid tns entry we get the result as shown below.

bash-3.2$ ./connect_db.sh db_user db_pwd invalid_db_tns
DB Connectivity failed ERROR: ORA-12154: TNS: could not resolve the connect identifier specified

3. Script to execute an sql via sqlplus from Linux.

#!/bin/bash
ORACLE_HOME=/app/a_oracle/product/11.1.0/client_1;export ORACLE_HOME
TNS_ADMIN=/home/myuser/tnsadmin;export TNS_ADMIN
PATH=$PATH:$ORACLE_HOME/bin;export PATH
sqlplus Username/Password@DB_TNSENTRY @query.sql >/dev/null

query.sql can contain sql commands to be executed. A sample query.sql is provided below. The output of the query will be spooled into employee.dat

bash-3.2$ more query.sql
set echo off
set heading off
set linesize 500
spool employee.dat
set trimspool on
set pagesize 0

select 'EMPLOYEE_ID~NAME' from dual
/
select emp_id||'~'||emp_name from employee where rownum < 50
/
spool off
exit
bash-3.2$

Leave a Reply

%d bloggers like this: