Skip to content

Variables: Shell Variables in SQL*PLUS

This is the second part of my blog about Shell scripting with focus on Oracle database.

Here is an example of how you can use shell variables in sqlplus to create dynamic sql.

Shell to SQLPLUS

Here I am using shell variable to determine my where clause. In some cases you may not know whether user is going to pass in exact table name or just part of it. I am using a variable called WHERE_C (for where clause).

[oracle@localhost ~]$ WHERE_C="like 'EMP%'"
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba << EOF 
select table_name from dba_tables 
where table_name ${WHERE_C}; 
EOF 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 12 16:14:18 2016 

Copyright (c) 1982, 2014, Oracle.  All rights reserved. 

Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL>
TABLE_NAME
--------------------------------------------------------------------------------
EMP
EMP
EMP
EMP
EMPLOYEES

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Now if I determine in my shell script that user has input exact tablename then all I have to do is update the WHERE_C variable and run the same sql to get the result.

[oracle@localhost ~]$ WHERE_C="= 'EMPLOYEES'"
[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba << EOF 
select table_name from dba_tables 
where table_name ${WHERE_C}; 
EOF 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 12 16:14:52 2016 

Copyright (c) 1982, 2014, Oracle. All rights reserved. 

Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL>
TABLE_NAME
--------------------------------------------------------------------------------
EMPLOYEES

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Here is an example of how you can run a single sql on multiple databases. Assuming you have all your database connections in tnsnames.ora and you a have user with same password in all of the database, you can run following example. Database names are in db_list.lis file in the current directory with one database name per line. In my test environment I only have one database, so I’ll use that three times.

[oracle@localhost ~]$ cat db_list.lis 
orcl
orcl
orcl
[oracle@localhost ~]$ cat run_multiple_db.ksh 
#!/bin/ksh

CONNECT_STRG="hr/oracle"
DB_LIST=./db_list.lis

cat ${DB_LIST} | while read LINE
do
print "Working on ${LINE} database"
sqlplus -s ${CONNECT_STRG}@${LINE} << EOF
select instance_name from v\$instance;
EOF
done


[oracle@localhost ~]$ ./run_multiple_db.ksh 
Working on orcl database

INSTANCE_NAME
----------------
cdb1

Working on orcl database

INSTANCE_NAME
----------------
cdb1

Working on orcl database

INSTANCE_NAME
----------------
cdb1

I have written many shell scripting to automate many tasks and to make my life easier. I’ll share more tips on shell scripting as I think of it.

That is all for now. Thanks for reading .

Published inOracle Database 11gR2Oracle Database 12cR1Shell Script

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *