Embedding SQL in Shell

Embedding a SQL inside Shell Script:-

Here is a tip on how to run sqlplus scripts within a shell script.

This is an example of how to pass database values into shell variables, and to make shell scripts more dynamic. This will help DBA to automate certain DBA tasks, also to setup some automation using cron.

#!/bin/sh
VAR1=`sqlplus -s username/password  <<end
set pagesize 0 feedback off ver off heading off echo off
select  sysdate from dual;
exit;
end`
echo “system date is ” $VAR1
#end of shell script

This will retrieve the system date from Oracle but you get the idea that you can expand the select script to get whatever you want from the database and place it in a shell variable where you can make decisions on it.

About the Author Atul Kumar

Leave a Comment:

2 comments
Gary says October 16, 2008

Yipes.
Firstly, DON’T put username/password on the command line where any monkey who can type ‘ps -ef’ can see it.
Secondly, sqlplus is supposed to be interactive. If you are scripting SQL, look at perl (or python or php). Error/exception handling is much easier and you can keep the connection open for the entire script, avoiding any repeated sqlplus startup/logon/logoff.

Reply
Rupesh says October 17, 2008

Appreciate your hint, I second you that username/password cannot be disclosed, but here this was a simple narration to show how we can embed SQL inside shell, well in advance you can use external files to store username/password.

This objective was not to make any interactive scripts, instead to automate some DBA tasks by placing embedded sql in shell.

Reply
Add Your Reply

Not found