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

User · Answer

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   lt  lt 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

User · Answer

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  lt  lt  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

User · Answer

For example   sqlplus -s admin password  lt  lt  EOF whenever sqlerror exit sql sqlcode  set echo off  set heading off   pl script 1 sql  pl script 2 sql  exit  EOF

User · Answer

Wouldn t something akin to this be better  security-wise    sqlplus -s  nolog  lt  lt  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

User · Answer

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  lt  lt EOF gt  gt  LOG FILE NAME user passwd host db  Your SQL code EOF

[bash] Connect to sqlplus in a shell script and run SQL scripts

Examples related to bash

Examples related to shell

Examples related to sqlplus