Running SQL*Plus scripts from Bash
Run SQL*Plus from a shell script with a here-document, stop at the first SQL error and spool query results to a text file.
SQL*Plus can run from a shell script: a here-document passes commands to it, and spool writes query results to a file. The examples below use placeholder credentials.
Run SQL scripts from a shell script
# Replace <password>; prefer OS authentication or a wallet over a password in a script
sqlplus -s dbuser/<password> << EOF
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
@pl_script_1.sql
@pl_script_2.sql
exit;
EOF
whenever sqlerror exit sql.sqlcode stops the session at the first SQL error and returns the SQL error code to the shell, so the script can detect the failure.
Export query results to a text file
Save the settings and the call in a script file, here getData.sql:
set heading off
set pages 0
set trimspool on
set lines 120
set feedback off
set echo off
set termout off
spool test.txt
@test.sql
spool off
set termout off hides the output on screen, and spool writes it to test.txt.
Run the script as SYSDBA
sqlplus / as sysdba @getData.sql