Skip to main content

sqlplus in shell scripts examples

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.