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?

score 48 · Accepted Answer · answered Apr 23 '12 at 09:40

48

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

answered Apr 23 '12 at 09:40

NetBear

642
1
7
7

12

This is horribly insecure, as anyone can see the password while "sqlplus" is running by using the "ps" command. – JPaget Apr 17 '13 at 14:30
Hi @NetBear, I think you need to do `sqlplus -s admin/password@server` (the `@server` part was the deal breaker in my case). – sturmer Apr 30 '14 at 15:11
use `sqlplus -s /@:/ << EOF` in the first line for the remote db – jeevan Jun 23 '21 at 07:31

score 34 · Answer 2 · answered Apr 19 '13 at 15:39

34

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

answered Apr 19 '13 at 15:39

Blaine DeLancey

440
5
4

1

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? – Keith Pinson Apr 19 '13 at 16:00
12

It's more secure because username/password doesn't show up in `ps -ea` – Kias Oct 22 '13 at 03:31
Chaos, thanks for adding that - yeah, that was my intent, and I didn't see keith's reply, so thanks for catching this! – Blaine DeLancey Feb 05 '17 at 14:28
3

For posterity: if you use Bash variables, do not surround them by double quotes after `CONNECT`. So `CONNECT "$ORCL_CREDS"` won't work. – Stefan van den Akker Feb 22 '17 at 09:55

score 6 · Answer 3 · answered Nov 01 '12 at 08:48

6

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

answered Nov 01 '12 at 08:48

darwinbaisa

688
2
8
13

score 4 · Answer 4 · edited Dec 23 '14 at 18:29

This should handle issue:

WHENEVER SQLERROR EXIT SQL.SQLCODE
SPOOL ${SPOOL_FILE}
$RC returns oracle's exit code
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

score 1 · Answer 5 · edited Feb 13 '19 at 19:36

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.

score 0 · Answer 6 · answered May 31 '21 at 07:30

0

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

answered May 31 '21 at 07:30

ketan chaudhari

1

Connect to sqlplus in a shell script and run SQL scripts

6 Answers6

Linked