LLI Wiki — My Digital Knowledge Base

Oracle: get a SQL result into a bind variable or a shell variable

Capture a query result in a SQL*Plus, PL/SQL or shell variable using sqlplus -s.

This note shows how to get the result of a SQL query into a SQL*Plus bind variable, a PL/SQL variable, or a shell variable.

Without a PL/SQL block

Use a SQL*Plus bind variable:

var controlFile   VARCHAR2(2000)
exec SELECT value into :controlFile FROM v$parameter WHERE name like '%control_files%';
print :controlFile

With a PL/SQL block

set serveroutput on
DECLARE
  controlFile varchar2(100);
BEGIN
  SELECT
    value
  into
    controlFile
  FROM v$parameter
  WHERE name like '%control_files%';
  dbms_output.put_line(controlFile);
END;
/

Return the value to a shell variable

Run SQL*Plus in silent mode inside a here-document and capture its output:

ret=`sqlplus -s / as sysdba  << end_sql
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TERM OFF
SET NEWPAGE NONE
WHENEVER SQLERROR EXIT 1 ROLLBACK
var controlFile   VARCHAR2(2000)
exec SELECT value into :controlFile FROM v\$parameter WHERE name like '%control_files%';
print :controlFile
exit 0;
end_sql`

echo "Your out parameter is: $ret"

Notes

Mastodon LLIMastodon SocialFosstodon