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$