How to Run Script in SQL Plus and Save the Output

There are several Oracle SQL*Plus questions often asked. How to run a SQL script in Oracle SQLPlus? How to execute a script file within SQL Plus environment? How to save the script output in SQLPlus to a text file?

In Oracle, use @ to run a script file after starting SQL Plus. SQL Plus can be started from any directory where the user is given the access to. The user account needs privilege to run sqlplus which is usually in the $ORACLE_HOME/bin folder. The following is the format to run a script in SQL Plus in the Linux environment:

SQL > @script.sql
SQL > @/path/script.sql

If the path is not specified, SQL Plus look for the script file in the directory where the current user account is at. You can use cd to change the directory to the location where the script is put, and then run SQL Plus. Otherwise, you can specify the path of the script in the @ command.

Examples

We need to run “DGPhyStby_script_v3.sql” which is provided by Oracle for Data Guard diagnostics. First, we download the script and upload it to our Oracle server (Linux) in the /oracle_staging directory. Then we start SQL Plus and run the script by its name.

In the first example, we change the path to /oracle_staging and then run SQL Plus and execute the script directly in SQL Plus environment.  In the second example, we start SQL Plus and execute the script by giving both path and file name.

[oracle@drodbp01 ~]$ cd /oracle_staging
[oracle@drodbp01 oracle_staging]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 10 13:07:50 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> @DGPhyStby_script_v3.sql

[oracle@hqodbp01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 10 13:19:41 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @/oracle_staging/DGPhyStby_script_v3.sql

Save SQL*Plus Output to a File

To save the output, use spool filename to start saving the output, and use spool off to end the end the saving. Let’s take a look of “DGPhyStby_script_v3.sql” script. The beginning of the script contains the following lines to prepare the output file to be saved:

set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(sysdate,'mmddyyyy_hh24miss') timecol, '.html' spool_extension FROM dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_unique_name';
spool dg_psbydiag_&&dbname&&timestamp&&suffix

The output file name is constructed by several parameters. The saved file is like:
dg_psbydiag_drcdbsblp_04102018_130935.html

The script has the following last lines to end the spooling of the output:

spool off
set markup html off entmap on

To investigate the whole script, click here: https://cl.ly/qmhw