{"id":169,"date":"2016-03-16T08:00:59","date_gmt":"2016-03-16T14:00:59","guid":{"rendered":"http:\/\/www.aamirharoon.com\/?p=169"},"modified":"2016-03-15T22:15:13","modified_gmt":"2016-03-16T04:15:13","slug":"variables-shell-variables-sqlplus","status":"publish","type":"post","link":"https:\/\/aamirharoon.com\/?p=169","title":{"rendered":"Variables: Shell Variables in SQL*PLUS"},"content":{"rendered":"<p>This is the second part of my blog about Shell scripting with focus on Oracle database.<\/p>\n<p>Here is an example of how you can use shell variables in sqlplus to create dynamic sql.<\/p>\n<h3>Shell to SQLPLUS<\/h3>\n<p>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).<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n&#x5B;oracle@localhost ~]$ WHERE_C=&quot;like 'EMP%'&quot;\r\n&#x5B;oracle@localhost ~]$ sqlplus sys\/oracle@orcl as sysdba &lt;&lt; EOF \r\nselect table_name from dba_tables \r\nwhere table_name ${WHERE_C}; \r\nEOF \r\n\r\nSQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 12 16:14:18 2016 \r\n\r\nCopyright (c) 1982, 2014, Oracle. \u00a0All rights reserved. \r\n\r\nConnected to: \r\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options \r\n\r\nSQL&gt;\r\nTABLE_NAME\r\n--------------------------------------------------------------------------------\r\nEMP\r\nEMP\r\nEMP\r\nEMP\r\nEMPLOYEES\r\n\r\nSQL&gt; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\r\n<\/pre>\n<p>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.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n&#x5B;oracle@localhost ~]$ WHERE_C=&quot;= 'EMPLOYEES'&quot;\r\n&#x5B;oracle@localhost ~]$ sqlplus sys\/oracle@orcl as sysdba &lt;&lt; EOF \r\nselect table_name from dba_tables \r\nwhere table_name ${WHERE_C}; \r\nEOF \r\n\r\nSQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 12 16:14:52 2016 \r\n\r\nCopyright (c) 1982, 2014, Oracle. All rights reserved. \r\n\r\nConnected to: \r\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options \r\n\r\nSQL&gt;\r\nTABLE_NAME\r\n--------------------------------------------------------------------------------\r\nEMPLOYEES\r\n\r\nSQL&gt; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\r\n<\/pre>\n<p>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&#8217;ll use that three times.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n&#x5B;oracle@localhost ~]$ cat db_list.lis \r\norcl\r\norcl\r\norcl\r\n&#x5B;oracle@localhost ~]$ cat run_multiple_db.ksh \r\n#!\/bin\/ksh\r\n\r\nCONNECT_STRG=&quot;hr\/oracle&quot;\r\nDB_LIST=.\/db_list.lis\r\n\r\ncat ${DB_LIST} | while read LINE\r\ndo\r\nprint &quot;Working on ${LINE} database&quot;\r\nsqlplus -s ${CONNECT_STRG}@${LINE} &lt;&lt; EOF\r\nselect instance_name from v\\$instance;\r\nEOF\r\ndone\r\n\r\n\r\n&#x5B;oracle@localhost ~]$ .\/run_multiple_db.ksh \r\nWorking on orcl database\r\n\r\nINSTANCE_NAME\r\n----------------\r\ncdb1\r\n\r\nWorking on orcl database\r\n\r\nINSTANCE_NAME\r\n----------------\r\ncdb1\r\n\r\nWorking on orcl database\r\n\r\nINSTANCE_NAME\r\n----------------\r\ncdb1\r\n<\/pre>\n<p>I have written many shell scripting to automate many tasks and to make my life easier. I&#8217;ll share more tips on shell scripting as I think of it.<\/p>\n<p>That is all for now. Thanks for reading\u00a0<img loading=\"lazy\" decoding=\"async\" style=\"margin-left: 3px; margin-right: 3px; vertical-align: middle; -webkit-box-shadow: none; -moz-box-shadow: none; box-shadow: none;\" src=\"https:\/\/www.aamirharoon.com\/wp-content\/plugins\/wp-emoji-one\/icons\/1F600.png\" alt=\"\" width=\"16\" height=\"16\" \/>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":172,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,40],"tags":[],"class_list":["post-169","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-11gr2","category-12cr1","category-shell-script"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":8}},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Variables: Shell Variables in SQL*PLUS - The Database Handyman<\/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:\/\/aamirharoon.com\/?p=169\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variables: Shell Variables in SQL*PLUS - The Database Handyman\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/aamirharoon.com\/?p=169\" \/>\n<meta property=\"og:site_name\" content=\"The Database Handyman\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-16T14:00:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png\" \/>\n\t<meta property=\"og:image:width\" content=\"654\" \/>\n\t<meta property=\"og:image:height\" content=\"500\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Aamir\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@aamir814\" \/>\n<meta name=\"twitter:site\" content=\"@aamir814\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Aamir\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169\"},\"author\":{\"name\":\"Aamir\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\"},\"headline\":\"Variables: Shell Variables in SQL*PLUS\",\"datePublished\":\"2016-03-16T14:00:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169\"},\"wordCount\":531,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\"},\"image\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png\",\"articleSection\":[\"Oracle Database 11gR2\",\"Oracle Database 12cR1\",\"Shell Script\"],\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/aamirharoon.com\\\/?p=169#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169\",\"url\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169\",\"name\":\"Variables: Shell Variables in SQL*PLUS - The Database Handyman\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png\",\"datePublished\":\"2016-03-16T14:00:59+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/aamirharoon.com\\\/?p=169\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#primaryimage\",\"url\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png\",\"contentUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png\",\"width\":654,\"height\":500},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/?p=169#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/aamirharoon.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variables: Shell Variables in SQL*PLUS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#website\",\"url\":\"https:\\\/\\\/aamirharoon.com\\\/\",\"name\":\"The Database Handyman\",\"description\":\"Cloud Architecture. Open-Source Databases. Data-Driven Solutions.\",\"publisher\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/aamirharoon.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\",\"name\":\"Aamir\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/cropped-IMG_2639-scaled-1.jpg\",\"url\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/cropped-IMG_2639-scaled-1.jpg\",\"contentUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/cropped-IMG_2639-scaled-1.jpg\",\"width\":512,\"height\":512,\"caption\":\"Aamir\"},\"logo\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/cropped-IMG_2639-scaled-1.jpg\"},\"sameAs\":[\"https:\\\/\\\/www.aamirharoon.com\\\/about\\\/\",\"https:\\\/\\\/x.com\\\/aamir814\"],\"url\":\"https:\\\/\\\/aamirharoon.com\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Variables: Shell Variables in SQL*PLUS - The Database Handyman","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:\/\/aamirharoon.com\/?p=169","og_locale":"en_US","og_type":"article","og_title":"Variables: Shell Variables in SQL*PLUS - The Database Handyman","og_description":"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 [&hellip;]","og_url":"https:\/\/aamirharoon.com\/?p=169","og_site_name":"The Database Handyman","article_published_time":"2016-03-16T14:00:59+00:00","og_image":[{"width":654,"height":500,"url":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png","type":"image\/png"}],"author":"Aamir","twitter_card":"summary_large_image","twitter_creator":"@aamir814","twitter_site":"@aamir814","twitter_misc":{"Written by":"Aamir","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/aamirharoon.com\/?p=169#article","isPartOf":{"@id":"https:\/\/aamirharoon.com\/?p=169"},"author":{"name":"Aamir","@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75"},"headline":"Variables: Shell Variables in SQL*PLUS","datePublished":"2016-03-16T14:00:59+00:00","mainEntityOfPage":{"@id":"https:\/\/aamirharoon.com\/?p=169"},"wordCount":531,"commentCount":0,"publisher":{"@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75"},"image":{"@id":"https:\/\/aamirharoon.com\/?p=169#primaryimage"},"thumbnailUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png","articleSection":["Oracle Database 11gR2","Oracle Database 12cR1","Shell Script"],"inLanguage":"en","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/aamirharoon.com\/?p=169#respond"]}]},{"@type":"WebPage","@id":"https:\/\/aamirharoon.com\/?p=169","url":"https:\/\/aamirharoon.com\/?p=169","name":"Variables: Shell Variables in SQL*PLUS - The Database Handyman","isPartOf":{"@id":"https:\/\/aamirharoon.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/aamirharoon.com\/?p=169#primaryimage"},"image":{"@id":"https:\/\/aamirharoon.com\/?p=169#primaryimage"},"thumbnailUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png","datePublished":"2016-03-16T14:00:59+00:00","breadcrumb":{"@id":"https:\/\/aamirharoon.com\/?p=169#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/aamirharoon.com\/?p=169"]}]},{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/aamirharoon.com\/?p=169#primaryimage","url":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png","contentUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/Screen-Shot-2016-03-15-at-10.05.56-PM-e1458098035715.png","width":654,"height":500},{"@type":"BreadcrumbList","@id":"https:\/\/aamirharoon.com\/?p=169#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/aamirharoon.com\/"},{"@type":"ListItem","position":2,"name":"Variables: Shell Variables in SQL*PLUS"}]},{"@type":"WebSite","@id":"https:\/\/aamirharoon.com\/#website","url":"https:\/\/aamirharoon.com\/","name":"The Database Handyman","description":"Cloud Architecture. Open-Source Databases. Data-Driven Solutions.","publisher":{"@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/aamirharoon.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":["Person","Organization"],"@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75","name":"Aamir","image":{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2020\/10\/cropped-IMG_2639-scaled-1.jpg","url":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2020\/10\/cropped-IMG_2639-scaled-1.jpg","contentUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2020\/10\/cropped-IMG_2639-scaled-1.jpg","width":512,"height":512,"caption":"Aamir"},"logo":{"@id":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2020\/10\/cropped-IMG_2639-scaled-1.jpg"},"sameAs":["https:\/\/www.aamirharoon.com\/about\/","https:\/\/x.com\/aamir814"],"url":"https:\/\/aamirharoon.com\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=169"}],"version-history":[{"count":8,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/169\/revisions"}],"predecessor-version":[{"id":180,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/169\/revisions\/180"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/media\/172"}],"wp:attachment":[{"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}