You will need to use sqlplus to connect to Oracle from a shell script. First its a good idea to set the

Oracle environment variables; if not all then set at least the last two of the following:

export ORACLE_BASE=/path/to/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11g
export ORACLE_TERM=xsun
export NLS_LANG=American_America.US7ASCII
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH

To connect using ORACLE_SID, set the oracle SID and run sqlplus in the following manner

export ORACLE_SID=your_oracle_sid
sqlplus -s LOGIN/PASSWD

The -s option suppresses the login banner etc.

However, if you have a Service Name instead and your oracle instance runs off of a different port than the default, then use the following format for connecting.

sqlplus -s LOGIN/PASSSWD@ORACLE_SERVERNAME:ORACLE_PORT/SERVICE_NAME

So how do we run sql queries? Check out the following script from lines 10 to 16. The output from the queries is redirected to a file (sqloutput in our case). The pagesize is set to 0 in order to facilitate easier parsing of the output file as this will suppress multiple column headings.

#!/usr/bin/ksh
export ORACLE_BASE=/path/to/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11g
export ORACLE_TERM=xsun
export NLS_LANG=American_America.US7ASCII
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s LOGIN/PASSSWD@SERVERNAME:ORA_PORT/SERVICE_NAME << EOF > sqloutput
set pagesize 0;
select * from blah;
update blah;
commit;
exit;
EOF

CAVEAT: Your oracle login credentials will be exposed in plain text. It is therefore preferable to read it in from a file stored securely.

Tags: oracle, Shell Scripting, Unix