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
$inv\$parameteris escaped so that the shell does not expand it.DBMS_OUTPUTshows nothing unlessset serveroutput onis active (in SQL Developer, enable the DBMS Output window instead).- Reference: SQL*Plus shell scripting.