Execute Oracle SQL queries from Linux

Here is a very simple script which helps you to run Oracle SQL queries from Linux environment.

Many if them try to use PUTTY to access the Oracle server i.e. on Linux , so this script can be modified and can be utilized in many ways….

First save the below script as “test.sh” and save it to any of the path on your Linux server:


#!/bin/sh
#---------------------------------------------------
# Export your Oracle details as below
#---------------------------------------------------
export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=<You Oracle SID>
# Here I am using Oracle 11.1.o version you can change it as per your database version
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH

#---------------------------------------------------
# Running a sql query on linux
# Here I trying to get the SYSDATE from Oracle you can change the query as per your need
#---------------------------------------------------
l_var=$(sqlplus -S USER@$ORACLE_SID/password <<EOF
SELECT sysdate FROM dual;

exit;
EOF)
# check if no records found or not
if [ -z "$l_var" ];
 then
 echo "No rows returned from database"
 exit 0
else
 echo $l_var
fi

The above code will connect to oracle database and run a SQL query to get the current sysdate of the server where it is located. You can change the code to check the time-stamp too .

Once you save this script then grant read and execute privileges to users if you want the same script to be executed by other linux users too.

To run the shell script type :


./test.sh

 

Advertisements

2 thoughts on “Execute Oracle SQL queries from Linux

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s