Variables: Shell, SQL*PLUS and PL/SQL

I am going to start blogging about shell scripting tips and technique focusing on database and how they can be used to make DBA life easier.

Here is an example of how to save result from SQL query into Shell variable.  Commands between backtick “`” or “$( )” are executed in a subshell and results are returned to parent shell.

Shell Variables:

sh> RESTORE_NAME=`sqlplus -s / as sysdba << EOF 
set head off feedback off 
select sysdate from dual; 
exit; 
EOF` 

sh> echo ${RESTORE_NAME}
03/10/2016 22:45:06

OR

sh> RESTORE_NAME=$(sqlplus -s / as sysdba << EOF 
set head off feedback off 
select sysdate from dual; 
exit; 
EOF) 

sh> echo ${RESTORE_NAME}
03/10/2016 22:45:50

SQL Variables

There are few different ways to declare, assign and retrieve variables in SQL*Plus.

DEFINE Command:

SQL> define sid = "some_sid" (CHAR)
SQL> select '&sid' from dual;
old 1: select '&sid' from dual
new 1: select 'some_sid' from dual

'SOME_SI
--------
some_sid

Using COLUMN Command

SQL> col instance_name new_value sid;
SQL> select instance_name from v$instance;
SQL> select '&sid' from dual;
old 1: select '&sid' from dual
new 1: select 'cdb1' from dual

'CDB
----
cdb1

VARIABLE Command:

SQL> var sqlplus_var varchar2(50);
SQL> exec select 'sqlplus variable' into :sqlplus_var from dual;

PL/SQL procedure successfully completed.

SQL> print sqlplus_var

SQLPLUS_VAR
----------------------------------------------------------------------------------------------------
sqlplus variable

--another way to assign value

SQL> exec :sqlplus_var := 'sqlplus variable exec';

PL/SQL procedure successfully completed.

SQL> print sqlplus_var;

SQLPLUS_VAR
----------------------------------------------------------------------------------------------------
sqlplus variable exec

PL/SQL Variable

This how you assign and display variables in PL/SQL

declare
 plsql_var varchar2(50);
 begin
   select 'plsql variable' into plsql_var from dual;
   dbms_output.put_line ('plsql_var =' || plsql_var);
 end;
/

plsql_var =plsql variable

PL/SQL procedure successfully completed.

Next I’ll write about how you can pass shell variables into sqlplus to create dynamic sql.