The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.
The Basic Syntax
In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:
#!/bin/sh sqlplus system/manager@prod_db <<ENDOFSQL select sysdate from dual; exit; ENDOFSQL
