Connect to sqlplus in a shell script and run SQL scripts

Wouldn't something akin to this be better, security-wise?:

sqlplus -S /nolog << EOF
CONNECT admin/password;

whenever sqlerror exit sql.sqlcode;

@pl_script_1.sql
@pl_script_2.sql

exit;
EOF 

Feeding the SQL statements to sqlplus on its standard input (via a here doc in this example) prevents the password from appearing in the process list.

If you need to specify a different host or service name, e.g. one defined in your tnsnames.ora, append @service_name to the CONNECT statement above.

If you want to redirect the output to a log file to look for errors or something. You can do something like this.

sqlplus -s <<EOF>> LOG_FILE_NAME user/passwd@host/db
#Your SQL code
EOF

This should handle issue:

1. WHENEVER SQLERROR EXIT SQL.SQLCODE
2. SPOOL ${SPOOL_FILE}
3. $RC returns oracle's exit code
4. cat from $SPOOL_FILE explains error

SPOOL_FILE=${LOG_DIR}/${LOG_FILE_NAME}.spool 

SQLPLUS_OUTPUT=`sqlplus -s  "$SFDC_WE_CORE" <<EOF 
        SET HEAD OFF
        SET AUTOPRINT OFF
        SET TERMOUT OFF
        SET SERVEROUTPUT ON

        SPOOL  ${SPOOL_FILE} 

        WHENEVER SQLERROR EXIT SQL.SQLCODE
        DECLARE 

        BEGIN
           foooo 
        --rollback; 
        END;
    /
    EOF` 

RC=$?

if [[ $RC != 0 ]] ; then

    echo " RDBMS exit code : $RC  "     | tee -a ${LOG_FILE}
    cat ${SPOOL_FILE}                   | tee -a ${LOG_FILE}

    cat ${LOG_FILE} | mail -s "Script ${INIT_EXE} failed on $SFDC_ENV" $SUPPORT_LIST

    exit 3

fi

If you are logging into sqlplus from a script, I would recommend setting up a wallet to store the username/password and tns

--create a wallet subdir
cd /myscriptloc/wallet
$ORACLE_HOME/client/bin/mkstore -wrl . -create
$ORACLE_HOME/client/bin/mkstore -wrl . -createCredential tnsname username pw

You will need the filessqlnet.ora and tnsnames.ora in this directory for the tns:

--sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
      (METHOD = FILE)
         (METHOD_DATA=
           (DIRECTORY=/myscriptloc/wallet)
         )
    )

SQLNET.WALLET_OVERRIDE=TRUE
SSL_CLIENT_AUTHENTICATION=FALSE
--end of sqlnet.ora

-- Script
TNS_ADMIN=/myscriptloc/wallet
tnsname=tnsname_from_wallet

sqlplus -S  /nolog <<stop
  connect /@${tnsname}
  @@${scriptname}
exit

Here's an answer that doesn't expose the logon credentials on the command line. It expands a bit on this answer above.

Of course, this script's file permissions should be set to only allow you to view and execute it, and keep in mind the root user can see your script.

#!/bin/bash

# set up any session parms
export SID=tradewinds

# logon information
export USER="scott"
export PW='tiger'

sqlplus /nolog  << EOF
connect $USER/$PW@$SID
@@disp_space.sql
exit
EOF
SQLPLUS_RC=$?

echo Sql Plus return code: $SQLPLUS_RC

Recent Oracle versions clear out any connect information on the command line, but there could be a brief moment where it is visible to all.

See this answer for using the wallet, which is more secure than this.

Some of the other answers here inspired me to write a script for automating the mixed sequential execution of SQL tasks using SQLPLUS along with shell commands for a project, a process that was previously manually done. Maybe this (highly sanitized) example will be useful to someone else:

#!/bin/bash
acreds="user_a/supergreatpassword"
bcreds="user_b/anothergreatpassword"
hoststring='fancyoraclehoststring'

runsql () {
  # param 1 is $1
sqlplus -S /nolog << EOF
CONNECT $1@$hoststring;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
$2
exit;
EOF
}

echo "TS::$(date): Starting SCHEM_A.PROC_YOU_NEED()..."
runsql "$acreds" "execute SCHEM_A.PROC_YOU_NEED();"

echo "TS::$(date): Starting superusefuljob..."
/var/scripts/superusefuljob.sh

echo "TS::$(date): Starting SCHEM_B.SECRET_B_PROC()..."
runsql "$bcreds" "execute SCHEM_B.SECRET_B_PROC();"

echo "TS::$(date): DONE"

runsql allows you to pass a credential string as the first argument, and any SQL you need as the second argument. The variables containing the credentials are included for illustration, but for security I actually source them from another file. If you wanted to handle multiple database connections, you could easily modify the function to accept the hoststring as an additional parameter.

echo "exit" | echo "SELECT table_name FROM all_tables FETCH FIRST 10 ROWS ONLY;" | sqlplus admin/password > outputFile.log