sqlplus access and email access using shell script..

Question

I am fairly beginner level at shell scripts and following are the details..

Am looking for the best way to fire sql queries and and carry out some logic based on that data. I've used the following snippet..

shellvariable=sqlplus $user/$passwd <<END select count(1) from table1; end EOF

if[$shellvariable -ne 0] then <> fi

Is there a better way to carry out the same..

Answer 1

hi @Satya, you're on the right track. sqlplus is the best way to interact with the database when you are shell scripting, but two things to note:

1. use the "-S" parameter to stop sqlplus from printing all its application info
2. to read data directly into a variable, you will need some sqlplus environment settings to prune back the output to just what you want

For any DBA's who are learning shell scripting to help them manage and automate Oracle database administration, I would highly recommend Jon Emmons' book Oracle Shell Scripting. It teaches a great shell scripting intro course, but in the context of tasks that are really useful and interesting to DBAs.

One final note: if you are doing anything any more than a simple DBA task, I would recommend not using shell scripts, but use a scripting language that has proper database support. Perl is a good option for Oracle, since it is installed with the database.

Here's an example of a script for Oracle done in both bash and perl. From the shell version, here's how it reads a specific value to a shell variable:

alertlog=$(sqlplus -S \/ as sysdba 2> /dev/null <<EOF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT value 
FROM   v\$parameter 
WHERE  name = 'background_dump_dest';
EOF
)

Answer 2

Accessing database through shell scripting