- 11:17 AM - 10 comments

Connecting to oracle database from unix shell script

How to connect to database through Unix Shell Script


In this post we will how to connect Oracle database through unix shell scripting

Connecting to oracle database and accessing data from shell script

$ cat sqlconnect.sh
#!/bin/bash
sqlplus / << EOFSQL
select sysdate from dual;
exit;
EOFSQL
$


We will get sysdate as output with Oracle banner
Output of above code

$ ./sqlconnect.sh
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 22 10:52:26 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SYSDATE
---------
22-AUG-09
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
$


To remove oracle banner from the output connect the database in silent mode (use -s option)

Connect to a database via UNIX Shell Script in silent mode

$ cat sqlconnect.sh
#!/bin/bash
sqlplus -s / << EOFSQL
select sysdate from dual;
exit;
EOFSQL
$ ./sqlconnect.sh
SYSDATE
---------
22-AUG-09
$


Connect to a Database in Shell Programming and storing the output in UNIX variable

Use back ticks to do the same as

$ cat sqlconnect.sh
#!/bin/bash
ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQL
set head off
select ename from emp where empno=7839;
exit;
EOFSQL`
echo emp name : $ORACLE_VALUE
$ ./sqlconnect.sh
emp name : KING
$


To get access two columns data and storing result in Unix variables

$ cat sqlconnect.sh
#!/bin/bash
ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQL
set head off
select empno':'ename from emp where empno=7839;
exit;
EOFSQL`
echo emp no : `echo $ORACLE_VALUE cut -d ":" -f1`
echo emp name : `echo $ORACLE_VALUE cut -d ":" -f2`
$ ./sqlconnect.sh
emp no : 7839
emp name : KING
$


How to get data from a database table into UNIX file using shell script

Using a Shell Script to connect to sql database and execute db query from a unix shell script

$ cat sqlconnect.sh
#!/bin/bash
sqlplus -s scott/tiger << EOFSQL
spool emp.dat
select ename from emp where empno=7839;
spool off
exit;
EOFSQL
$ ./sqlconnect.sh

ENAME
----------
KING
$ ls -ltrtail -1
-rw-r--r-- 1 scripter scripterworld 245 Aug 22 10:55 emp.dat
$


Startup and shutdown Oracle Database with UNIX shell script

To startup oracle database from UNIX shell script

sqlplus -s "/ as sysdba" << EOF
startup;
exit;
EOF


To shutdown oracle database from unix shell script

sqlplus -s "/ as sysdba" << EOF
shutdown immediate;
exit;
EOF



UNIX shell script to run SQL files as

sqlplus -s scott/tiger << EOF
@emp.sql
@dept.sql
exit;
EOF



To pass unix variable from shell to Oracle database.

$ cat sqlconnect.sh
#!/bin/bash

echo -n "Enter empno number : "
read VALEMP

if [ -z "${VALEMP}" ]
then
echo "You shoul enter empno."
exit 1
fi

ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQL
set head off
select empno||':'||ename from emp where empno=${VALEMP};
exit;
EOFSQL`

echo emp no : `echo $ORACLE_VALUE | cut -d ":" -f1`
echo emp name : `echo $ORACLE_VALUE | cut -d ":" -f2`
$ ./sqlconnect.sh
Enter empno number : 7839
emp no : 7839
emp name : KING
$



To Call Shell script from SQL use HOST keyword

HOST /home/scripter/scripts/myshell.sh


Please give your valuable doubts or feedback via comments

10 Responses to "Connecting to oracle database from unix shell script"

Scripter Says :
August 31, 2009 7:52 PM

To pass unix variable from shell to Oracle database.

$ cat sqlconnect.sh
#!/bin/bash

echo -n "Enter empno number : "
read VALEMP

if [ -z "${VALEMP}" ]
then
echo "You shoul enter empno."
exit 1
fi

ORACLE_VALUE=`sqlplus -s scott/tiger << EOFSQL
set head off
select empno||':'||ename from emp where empno=${VALEMP};
EOFSQL`

echo emp no : `echo $ORACLE_VALUE | cut -d ":" -f1`
echo emp name : `echo $ORACLE_VALUE | cut -d ":" -f2`
$ ./sqlconnect.sh
Enter empno number : 7839
emp no : 7839
emp name : KING
$

Anonymous Says :
December 8, 2009 11:45 PM

great information....really appreciate

Anonymous Says :
May 11, 2010 5:32 AM

Thanks for such neat explanation...

Anonymous Says :
June 14, 2010 6:32 AM

This really helpped me a lot!

I tried to run the full shutdown immediate for the database using one command (single shell line), but I couldn't make it working.

Regards

Karthi Says :
June 24, 2010 7:40 AM

thanks alot really usefull man....

budu Says :
March 18, 2011 2:36 AM

its very much help full

Swagatika Says :
March 22, 2011 11:59 PM

This is really helpful.
Thanks a lot but could you please answer how to connect to oracle remotely through shell script?

Anonymous Says :
March 26, 2011 10:52 PM

Hey,

Great work! Carry on this is awesome!

Appreciated:)

Anonymous Says :
June 26, 2011 10:22 PM

Hey its amazing dude!!Its really help me a lot after 3 days of workout.

Thanks a ton!!!

Anonymous Says :
August 2, 2011 2:50 AM

Very nicely explained .. Great work

Post a Comment