Connect to sqlplus in a shell script and run SQL scripts

Question

I have a .sql file, which is a bunch of oracle pl/sql commands and I want to create a shell script to run these commands.

Suppose that user/pass@server is my credentials. What will be the shell script to do such a task?

Solution 1

For example:

sqlplus -s admin/password << EOF
whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off

@pl_script_1.sql
@pl_script_2.sql

exit;
EOF

Solution 2

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

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

whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off

@pl_script_1.sql
@pl_script_2.sql

exit;
EOF 

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Roeland]

This is horribly insecure, as anyone can see the password while "sqlplus" is running by using the "ps" command.

[Keith Pinson]

What is this more secure than? What makes it more secure? Try editing your post and adding this information. Or are you asking a question?

[Chaos]

It's more secure because username/password doesn't show up in ps -ea

[sturmer]

Hi @NetBear, I think you need to do sqlplus -s admin/password@server (the @server part was the deal breaker in my case).

[Blaine DeLancey]

Chaos, thanks for adding that - yeah, that was my intent, and I didn't see keith's reply, so thanks for catching this!

[Stefan van den Akker]

For posterity: if you use Bash variables, do not surround them by double quotes after CONNECT. So CONNECT "$ORCL_CREDS" won't work.

[jeevan]

use sqlplus -s <username>/<password>@<db_ip>:<db_port>/<db_name> << EOF in the first line for the remote db