Connect Oracle Database From Shell Script

There are two ways to connect Oracle from shell script.

1. You need to install SQL Plus Client on your server

2. You'll have install ORACLE on your server.

I share example for both.

Create file Oracle-ShellScript.sh

sudo nano Oracle-ShellScript.sh

Example with $ORACLE_HOME, add next lines into file. Check your path ORACLE_HOME

#!/bin/sh

#parameters DB

DATABASE_USER=$1

DATABASE_PASS=$2

IP=$3

SID=$4

DATE=`date +"%d%m%y"`

u="$USER"

echo "User name $u"

cd /home/$u

LOCATION=$PWD

echo "path ${LOCATION}"

ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

export ORACLE_HOME

echo $ORACLE_HOME

dbConexion() {

$ORACLE_HOME/bin/sqlplus -s <<EOF> ${LOCATION}/log${DATE}.out

$DATABASE_USER/$DATABASE_PASS@//$IP:1521/$SID

select sysdate from dual;

EOF

}

dbValidate() {

p=`cat ${LOCATION}/log${DATE}.out | grep SYSDATE`

if [ -n "$p" ]

then

echo "Ok conexion"

rm ${LOCATION}/log${DATE}.out

else

echo "Bad conexion."

exit

fi

}

echo "Start process"

dbConexion

echo "Check conexion on DB"

dbValidate

echo "End process"

Example with SQL Plus client add next lines into file:

#!/bin/sh

#parameters DB

DATABASE_USER=$1

DATABASE_PASS=$2

IP=$3

SID=$4

DATE=`date +"%d%m%y"`

u="$USER"

echo "User name $u"

cd /home/$u

LOCATION=$PWD

echo "path ${LOCATION}"

dbConexion() {

sqlplus64 -s <<EOF> ${LOCATION}/log${DATE}.out

$DATABASE_USER/$DATABASE_PASS@//$IP:1521/$SID

select sysdate from dual;

EOF

}

dbValidate() {

p=`cat ${LOCATION}/log${DATE}.out | grep SYSDATE`

if [ -n "$p" ]

then

echo "Ok conexion"

rm ${LOCATION}/log${DATE}.out

else

echo "Bad conexion."

exit

fi

}

echo "Start process"

dbConexion

echo "Check conexion on DB"

dbValidate

echo "End process"

Test: Send the next argument to shell script user_db, password_db, ip and sid

sh Oracle-ShellScript.sh YOUR_USER_DB YOUR_PASSWORD_DB YOUR_IP YOUR_SID

Code on GitHub: https://github.com/HenryXiloj/Connect-to-oracle-shell-script