{"id":22966,"date":"2023-02-27T14:41:12","date_gmt":"2023-02-27T13:41:12","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=22966"},"modified":"2025-01-24T10:43:31","modified_gmt":"2025-01-24T09:43:31","slug":"shell-scripting-improve-dba-scripts-readability-using-j2-templates","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/","title":{"rendered":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates"},"content":{"rendered":"\n<p>by Alexandre Nestor<\/p>\n\n\n\n<p id=\"tw-target-text\">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. <\/p>\n\n\n\n<p>Templating simplifies the script by taking out the SQL code from the source code file. J2 is ideally built for that. <\/p>\n\n\n\n<p>Let&#8217;s take a simple example: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n$&gt; cat sql_in_shell.sh\n\n#!\/bin\/bash\n. oraenv &lt;&lt;&lt; $1\n\ntable=$2\ncol1=$3\nval1=$4\n\nsqlplus -s \/ as sysdba &lt;&lt; EOF\nselect count(*)\nfrom $table\nwhere $col1=$val1\n;\nexit;\nEOF\n<\/pre><\/div>\n\n\n<p>Ok, this is a simple one, but in real world the queries are much complicated, and variables too&#8230;<\/p>\n\n\n\n<p>The execution produces the following output: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n$&gt; sql_in_shell.sh MYDBNAME v\\$instance instance_name \\&#039;MYDBNAME\\&#039;\n\n  COUNT(*)\n----------\n\t 1\n<\/pre><\/div>\n\n\n<p>Doing it like this it is very difficult to find the real query executed by the script&#8230; <\/p>\n\n\n\n<p>Let&#8217;s use J2 templates. <\/p>\n\n\n\n<p>We put all templates in a <code>template<\/code> directory and generated sql files in the <code>onrun<\/code> directory.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: yaml; title: ; notranslate\" title=\"\">\n$&gt; cat templates\/qry.j2\nselect count(*)\nfrom {{ table }}\nwhere {{ col1 }}={{ val1 }}\n;\nexit;\n<\/pre><\/div>\n\n\n<p>The script becomes: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n$ cat .\/sql_in_shell.sh\n#!\/bin\/bash\n\n#. oraenv &lt;&lt;&lt; $1\n\necho &quot;$1&quot;\n\nj2_sql=&quot;qry.sql&quot;\nj2_templ=&quot;qry.j2&quot;\nexport table=$2\nexport col1=$3\nexport val1=$4\n\nj2 templates\/$j2_templ &gt; onrun\/$j2_sql\n\nsqlplus -s \/ as sysdba @onrun\/$j2_sql\n<\/pre><\/div>\n\n\n<p>The output is the same: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n$&gt; sql_in_shell.sh MYDBNAME v\\$instance instance_name \\&#039;MYDBNAME\\&#039;\n\n  COUNT(*)\n----------\n\t 1\n<\/pre><\/div>\n\n\n<p>Now we have the executed query in the <code>onrun<\/code> directory: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n$ cat onrun\/qry.sql\nselect count(*)\nfrom v$instance\nwhere instance_name=&#039;MYDBNAME&#039;\n;\nexit;\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-resources\">Resources<\/h2>\n\n\n\n<p>J2 client can be found here: <a href=\"https:\/\/github.com\/kolypto\/j2cli\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/kolypto\/j2cli<\/a>. The site provides also an excelent documentation. <\/p>\n\n\n\n<p>Ubuntu provides already deb packages and for rpm&#8217;s an additional repository must be added following: <a href=\"https:\/\/snapcraft.io\/install\/j2\/rhel\">https:\/\/snapcraft.io\/install\/j2\/rhel<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion <\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Quering database&#8217;s by using SQL in shell scripts can make the whole script unreadable. Very oftent the query is unredable too, due to the variable usage. This situation make the script debugging a pain.<br \/>\nTemplating simplify the script by taking out from the soufce code file the SQL code. J2 is ideally built for that.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368,1320,59],"tags":[2777,96,644,1156],"type_dbi":[],"class_list":["post-22966","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-devops","category-oracle","tag-jinja","tag-oracle","tag-performance-tuning","tag-shell"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SHELL SCRIPTING: improve DBA scripts readability using J2 templates - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SHELL SCRIPTING: improve DBA scripts readability using J2 templates\" \/>\n<meta property=\"og:description\" content=\"Quering database&#039;s by using SQL in shell scripts can make the whole script unreadable. Very oftent the query is unredable too, due to the variable usage. This situation make the script debugging a pain. Templating simplify the script by taking out from the soufce code file the SQL code. J2 is ideally built for that.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-27T13:41:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-24T09:43:31+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"SHELL SCRIPTING: improve DBA scripts readability using J2 templates\",\"datePublished\":\"2023-02-27T13:41:12+00:00\",\"dateModified\":\"2025-01-24T09:43:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\"},\"wordCount\":229,\"commentCount\":0,\"keywords\":[\"jinja\",\"Oracle\",\"Performance Tuning\",\"Shell\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Development &amp; Performance\",\"DevOps\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\",\"name\":\"SHELL SCRIPTING: improve DBA scripts readability using J2 templates - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-02-27T13:41:12+00:00\",\"dateModified\":\"2025-01-24T09:43:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SHELL SCRIPTING: improve DBA scripts readability using J2 templates\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/","og_locale":"en_US","og_type":"article","og_title":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates","og_description":"Quering database's by using SQL in shell scripts can make the whole script unreadable. Very oftent the query is unredable too, due to the variable usage. This situation make the script debugging a pain. Templating simplify the script by taking out from the soufce code file the SQL code. J2 is ideally built for that.","og_url":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/","og_site_name":"dbi Blog","article_published_time":"2023-02-27T13:41:12+00:00","article_modified_time":"2025-01-24T09:43:31+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates","datePublished":"2023-02-27T13:41:12+00:00","dateModified":"2025-01-24T09:43:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/"},"wordCount":229,"commentCount":0,"keywords":["jinja","Oracle","Performance Tuning","Shell"],"articleSection":["Database Administration &amp; Monitoring","Database management","Development &amp; Performance","DevOps","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/","url":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/","name":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-02-27T13:41:12+00:00","dateModified":"2025-01-24T09:43:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/shell-scripting-improve-dba-scripts-readability-using-j2-templates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SHELL SCRIPTING: improve DBA scripts readability using J2 templates"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/22966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=22966"}],"version-history":[{"count":13,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/22966\/revisions"}],"predecessor-version":[{"id":36879,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/22966\/revisions\/36879"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=22966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=22966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=22966"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=22966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}