Sqlplus access and email access using shell script
I am pretty beginner in shell scripting and am following the details.
Looking for a better way to run sql queries and execute some logic based on that data. I used the following snippet.
shellvariable = sqlplus $user/$passwd <<END
select count(1) from table1;
end
EOF
if [$ shellvariable -ne 0] then <> q
Is there a better way to accomplish the same.
source to share
you are on the right track. sqlplus is the best way to interact with the database when you are a shell, but two things need to be noted:
- use the "-S" option to stop sqlplus from printing all your application info
- to read data directly into a variable, you will need some sqlplus setup to cut the output to just what you want
For any DBA who is learning shell scripting to help them manage and automate Oracle database administration, I highly recommend John Emmons' Oracle Shell Scripting book . It teaches an excellent tutorial for scripting, but in the context of tasks that are really useful and interesting for DBAs.
One final note, if you are doing anything simpler DBA assignment, I would recommend not using shell scripting, but using a scripting language that has proper database support. Perl is a good option for Oracle as it is installed with the database.
Here is an example for a script for Oracle, executed in both bash and perl. From the shell version, the specific value of the shell variable is read here:
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
)
source to share