Connect to Oracle DB with Linux Shell Script

In this post, we will see how to connect to Oracle DB in Linux Shell Script. There are some prerequisite for a Linux server to connect to Oracle DB. There should be oracle db driver installed on the Linux server from which you want to connect to Oracle DB. Check whether you can call/connect SQLPLUS from command line.
$ sqlplus
if you are not able to, check whether oracle path is set in .profile file. Lets assume, Oracle DB driver is installed on Server and Sqlplus path is set.

Now, there are some basic steps to connect to Oracle DB-
a) Connect to Oracle DB with UserID and Password
b) Execute the SQLs
c) Store the output in a file or send to standard output (terminal)


A) Connect to Oracle DB and send the SQL output to Standard Output (terminal)
========================================================================

	#!/bin/ksh
	#Fetching the Emp info which has EmpID='10'
	#invoking SQLPLUS with silent and nolog
	sqlplus -s /nolog << EOF
	CONNECT "$USER"/"$PASS"@"$DB"
	SET LINESIZE 100
	Select * from EMP where EmpID='10';
	EXIT;
	EOF

view raw OraConnect1.sh hosted with ❤ by GitHub

========================================================================

B) Connect to Oracle DB and assign the output a variable for output formatting
========================================================================

	#!/bin/ksh
	#Fetching the Emp info which has EmpID='10'
	VAL=`sqlplus -s /nolog << EOF
	CONNECT "$USER"/"$PASS"@"$DB"
	SET LINESIZE 100
	Select * from EMP where EmpID='10';
	EXIT;
	EOF`
	echo "$VAL"

view raw OraConnect2.sh hosted with ❤ by GitHub

========================================================================

C) Connect to Oracle DB and send the Output to a File

========================================================================

	#!/bin/ksh
	#Fetching the Emp info which has EmpID='10'
	#invoking SQLPLUS with silent and nolog
	sqlplus -s /nolog << EOF
	CONNECT "$USER"/"$PASS"@"$DB"
	SET LINESIZE 100
	SPOOL OutputFilePath/Filename
	Select * from EMP where EmpID='10';
	SPOOL OFF
	EXIT;
	EOF

view raw OraConnect3.sh hosted with ❤ by GitHub

========================================================================


Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098