Using sqlplus in scripts

Save an SQL SELECT statement output to a variable#

var=$(sqlplus -S <username>/<userpassword> <<END
set pagesize 0 feedback off verify off heading off echo off;
<SELECT statement>;
exit;
END
)

Executing a command constructed following the structure above lets you get the SQL SELECT query result without any extra output. The query result is assigned to the var variable.

Command explanation:

• a HERE-document is used to feed SQL commands to sqlplus:

<<END
...
END

• sqlplus -S executes sqlplus command in silent mode, without:
  • the display of the SQL*Plus banner,
  • the display of prompts,
  • echoing the commands,
• set pagesize 0 suppresses the following formatting for SQL statement results:
  • headings,
  • page breaks,
  • titles,
  • the initial blank line,
• set feedback off removes the PL/SQL procedure successfully completed messages displayed after successful SQL statements execution,
• set verify off removes the display of a list of variables in SQL statements, which are replaced by the corresponding values before the statements execution,
• set headings off removes the columns headings from the output,
• set echo off removes the display of the commands, in case you replace the HERE document (like in the example above) by an SQL script.