Shell Script to execute query by connecting Database

Hi All,

This post will give some idea about how to connect Database using shell script.

I am considering here an Oracle DB since it is used most widely in the industry. Along with Oracle, SQLPLUS is installed on your system. While connecting Oracle DB as a remote machine, please make sure you have SQLPLUS installed on your machine (i.e. from where you are connecting the DB or running this script).

You can simply test whether the SQLPLUS is installed on your machine as below.

$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 1 07:30:12 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name:
If it is already installed then it will prompt username as above else will give you an error. Once you confirmed please try the below script (i.e. this is a sample query to show how to connect DB and execute the query)

#!/bin/ksh

#FileName: dbconnect.ksh

SQL="set feedback off underline off pages 0 lines 999 heading off
           select sysdate from dual;"

echo -e "$SQL"| sqlplus -s userid/password@TNSNAME

I have store the above script with name as dbconnect.ksh, here userid is the userid for your schema and password is the password used for the given userid. TNSNAME is nothing but the connection string you used to connect the DB.

Store the script and check if any syntx error using below command on command prompt

$ /bin/ksh -n dbconnect.ksh

Once having no error, please execute the below script by changing its permission
$chmod 775 dbconnect.ksh
$ /bin/ksh dbconnect.ksh

01-SEP-13

You can see the output as current date.

This is one of the way to execute the query, there are others ways to to connect the Database.
I hope this post is useful to you.

Thanks