Skip to content

SHELL SCRIPTING: improve DBA scripts readability using J2 templates

by Alexandre Nestor

Quering databases by using SQL in shell scripts can make the whole script unreadable. Very often the query is unredable too, due to the variable usage. This situation makes the script-debugging a pain.

Templating simplifies the script by taking out the SQL code from the source code file. J2 is ideally built for that.

Let’s take a simple example:

$> cat sql_in_shell.sh

#!/bin/bash
. oraenv <<< $1

table=$2
col1=$3
val1=$4

sqlplus -s / as sysdba << EOF
select count(*)
from $table
where $col1=$val1
;
exit;
EOF

Ok, this is a simple one, but in real world the queries are much complicated, and variables too…

The execution produces the following output:

$> sql_in_shell.sh MYDBNAME v\$instance instance_name \'MYDBNAME\'

  COUNT(*)
----------
	 1

Doing it like this it is very difficult to find the real query executed by the script…

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)

Let’s use J2 templates.

We put all templates in a template directory and generated sql files in the onrun directory.

$> cat templates/qry.j2
select count(*)
from {{ table }}
where {{ col1 }}={{ val1 }}
;
exit;

The script becomes:

$ cat ./sql_in_shell.sh
#!/bin/bash

#. oraenv <<< $1

echo "$1"

j2_sql="qry.sql"
j2_templ="qry.j2"
export table=$2
export col1=$3
export val1=$4

j2 templates/$j2_templ > onrun/$j2_sql

sqlplus -s / as sysdba @onrun/$j2_sql

The output is the same:

$> sql_in_shell.sh MYDBNAME v\$instance instance_name \'MYDBNAME\'

  COUNT(*)
----------
	 1

Now we have the executed query in the onrun directory:

$ cat onrun/qry.sql
select count(*)
from v$instance
where instance_name='MYDBNAME'
;
exit;

Resources

J2 client can be found here: https://github.com/kolypto/j2cli. The site provides also an excelent documentation.

Ubuntu provides already deb packages and for rpm’s an additional repository must be added following: https://snapcraft.io/install/j2/rhel

Conclusion

Templating brings shell coding in to a more modern world, liberating the code source of difficult to understand variables replacement. More than that, keeping all executed queries, is a best practice and makes the debugging much easier.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

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