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 
.
