{"id":144,"date":"2016-03-11T09:00:48","date_gmt":"2016-03-11T15:00:48","guid":{"rendered":"http:\/\/www.aamirharoon.com\/?p=144"},"modified":"2016-03-15T22:07:08","modified_gmt":"2016-03-16T04:07:08","slug":"variables-shell-sqlplus-plsql","status":"publish","type":"post","link":"https:\/\/aamirharoon.com\/?p=144","title":{"rendered":"Variables: Shell, SQL*PLUS and PL\/SQL"},"content":{"rendered":"<p>I am going to start blogging about shell scripting tips and\u00a0technique focusing on database and how they can be used to make DBA life easier.<\/p>\n<p>Here is an\u00a0example of how to save result from SQL query into Shell variable. \u00a0Commands between backtick &#8220;`&#8221; or &#8220;$( )&#8221; are executed in a subshell and results are returned to parent shell.<\/p>\n<h2>Shell Variables:<\/h2>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\nsh&gt; RESTORE_NAME=`sqlplus -s \/ as sysdba &lt;&lt; EOF \r\nset head off feedback off \r\nselect sysdate from dual; \r\nexit; \r\nEOF` \r\n\r\nsh&gt; echo ${RESTORE_NAME}\r\n03\/10\/2016 22:45:06\r\n<\/pre>\n<p><span style=\"font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; line-height: 1.5;\">OR<br \/>\n<\/span><\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\nsh&gt; RESTORE_NAME=$(sqlplus -s \/ as sysdba &lt;&lt; EOF \r\nset head off feedback off \r\nselect sysdate from dual; \r\nexit; \r\nEOF) \r\n\r\nsh&gt; echo ${RESTORE_NAME}\r\n03\/10\/2016 22:45:50\r\n<\/pre>\n<h2>SQL Variables<\/h2>\n<p>There are few different ways to declare, assign and retrieve variables in SQL*Plus.<\/p>\n<p>DEFINE Command:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; define sid = &quot;some_sid&quot; (CHAR)\r\nSQL&gt; select '&amp;sid' from dual;\r\nold 1: select '&amp;sid' from dual\r\nnew 1: select 'some_sid' from dual\r\n\r\n'SOME_SI\r\n--------\r\nsome_sid\r\n\r\n<\/pre>\n<p>Using COLUMN Command<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; col instance_name new_value sid;\r\nSQL&gt; select instance_name from v$instance;\r\nSQL&gt; select '&amp;sid' from dual;\r\nold 1: select '&amp;sid' from dual\r\nnew 1: select 'cdb1' from dual\r\n\r\n'CDB\r\n----\r\ncdb1\r\n\r\n<\/pre>\n<p>VARIABLE Command:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; var sqlplus_var varchar2(50);\r\nSQL&gt; exec select 'sqlplus variable' into :sqlplus_var from dual;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL&gt; print sqlplus_var\r\n\r\nSQLPLUS_VAR\r\n----------------------------------------------------------------------------------------------------\r\nsqlplus variable\r\n\r\n--another way to assign value\r\n\r\nSQL&gt; exec :sqlplus_var := 'sqlplus variable exec';\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL&gt; print sqlplus_var;\r\n\r\nSQLPLUS_VAR\r\n----------------------------------------------------------------------------------------------------\r\nsqlplus variable exec\r\n\r\n<\/pre>\n<h2>PL\/SQL Variable<\/h2>\n<p>This how you assign and display variables in PL\/SQL<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ndeclare\r\n plsql_var varchar2(50);\r\n begin\r\n   select 'plsql variable' into plsql_var from dual;\r\n   dbms_output.put_line ('plsql_var =' || plsql_var);\r\n end;\r\n\/\r\n\r\nplsql_var =plsql variable\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n<\/pre>\n<p>Next I&#8217;ll write\u00a0about how you can pass shell variables into sqlplus to create dynamic sql.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am going to start blogging about shell scripting tips and\u00a0technique focusing on database and how they can be used to make DBA life easier. Here is an\u00a0example of how to save result from SQL query into Shell variable. \u00a0Commands between backtick &#8220;`&#8221; or &#8220;$( )&#8221; are executed in a subshell and results are returned [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":165,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,40],"tags":[44,41,43,42],"class_list":["post-144","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-11gr2","category-12cr1","category-shell-script","tag-plsql","tag-shell","tag-sqlplus","tag-variables"],"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, SQL*PLUS and PL\/SQL - 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:\/\/www.aamirharoon.com\/?p=144\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variables: Shell, SQL*PLUS and PL\/SQL - The Database Handyman\" \/>\n<meta property=\"og:description\" content=\"I am going to start blogging about shell scripting tips and\u00a0technique focusing on database and how they can be used to make DBA life easier. Here is an\u00a0example of how to save result from SQL query into Shell variable. \u00a0Commands between backtick &#8220;`&#8221; or &#8220;$( )&#8221; are executed in a subshell and results are returned [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.aamirharoon.com\/?p=144\" \/>\n<meta property=\"og:site_name\" content=\"The Database Handyman\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-11T15:00:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-03-16T04:07:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"728\" \/>\n\t<meta property=\"og:image:height\" content=\"546\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144\"},\"author\":{\"name\":\"Aamir\",\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\"},\"headline\":\"Variables: Shell, SQL*PLUS and PL\\\/SQL\",\"datePublished\":\"2016-03-11T15:00:48+00:00\",\"dateModified\":\"2016-03-16T04:07:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144\"},\"wordCount\":360,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#\\\/schema\\\/person\\\/5b7250c4974dcf9818ca431e21831c75\"},\"image\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/basic-variables-ppt-1-728.jpg\",\"keywords\":[\"plsql\",\"Shell\",\"sqlplus\",\"variables\"],\"articleSection\":[\"Oracle Database 11gR2\",\"Oracle Database 12cR1\",\"Shell Script\"],\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144\",\"url\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144\",\"name\":\"Variables: Shell, SQL*PLUS and PL\\\/SQL - The Database Handyman\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/aamirharoon.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/basic-variables-ppt-1-728.jpg\",\"datePublished\":\"2016-03-11T15:00:48+00:00\",\"dateModified\":\"2016-03-16T04:07:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#primaryimage\",\"url\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/basic-variables-ppt-1-728.jpg\",\"contentUrl\":\"https:\\\/\\\/aamirharoon.com\\\/wp-content\\\/uploads\\\/2016\\\/03\\\/basic-variables-ppt-1-728.jpg\",\"width\":728,\"height\":546},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.aamirharoon.com\\\/?p=144#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/aamirharoon.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variables: Shell, SQL*PLUS and PL\\\/SQL\"}]},{\"@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, SQL*PLUS and PL\/SQL - 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:\/\/www.aamirharoon.com\/?p=144","og_locale":"en_US","og_type":"article","og_title":"Variables: Shell, SQL*PLUS and PL\/SQL - The Database Handyman","og_description":"I am going to start blogging about shell scripting tips and\u00a0technique focusing on database and how they can be used to make DBA life easier. Here is an\u00a0example of how to save result from SQL query into Shell variable. \u00a0Commands between backtick &#8220;`&#8221; or &#8220;$( )&#8221; are executed in a subshell and results are returned [&hellip;]","og_url":"https:\/\/www.aamirharoon.com\/?p=144","og_site_name":"The Database Handyman","article_published_time":"2016-03-11T15:00:48+00:00","article_modified_time":"2016-03-16T04:07:08+00:00","og_image":[{"width":728,"height":546,"url":"https:\/\/www.aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg","type":"image\/jpeg"}],"author":"Aamir","twitter_card":"summary_large_image","twitter_creator":"@aamir814","twitter_site":"@aamir814","twitter_misc":{"Written by":"Aamir","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.aamirharoon.com\/?p=144#article","isPartOf":{"@id":"https:\/\/www.aamirharoon.com\/?p=144"},"author":{"name":"Aamir","@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75"},"headline":"Variables: Shell, SQL*PLUS and PL\/SQL","datePublished":"2016-03-11T15:00:48+00:00","dateModified":"2016-03-16T04:07:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.aamirharoon.com\/?p=144"},"wordCount":360,"commentCount":0,"publisher":{"@id":"https:\/\/aamirharoon.com\/#\/schema\/person\/5b7250c4974dcf9818ca431e21831c75"},"image":{"@id":"https:\/\/www.aamirharoon.com\/?p=144#primaryimage"},"thumbnailUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg","keywords":["plsql","Shell","sqlplus","variables"],"articleSection":["Oracle Database 11gR2","Oracle Database 12cR1","Shell Script"],"inLanguage":"en","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.aamirharoon.com\/?p=144#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.aamirharoon.com\/?p=144","url":"https:\/\/www.aamirharoon.com\/?p=144","name":"Variables: Shell, SQL*PLUS and PL\/SQL - The Database Handyman","isPartOf":{"@id":"https:\/\/aamirharoon.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.aamirharoon.com\/?p=144#primaryimage"},"image":{"@id":"https:\/\/www.aamirharoon.com\/?p=144#primaryimage"},"thumbnailUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg","datePublished":"2016-03-11T15:00:48+00:00","dateModified":"2016-03-16T04:07:08+00:00","breadcrumb":{"@id":"https:\/\/www.aamirharoon.com\/?p=144#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.aamirharoon.com\/?p=144"]}]},{"@type":"ImageObject","inLanguage":"en","@id":"https:\/\/www.aamirharoon.com\/?p=144#primaryimage","url":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg","contentUrl":"https:\/\/aamirharoon.com\/wp-content\/uploads\/2016\/03\/basic-variables-ppt-1-728.jpg","width":728,"height":546},{"@type":"BreadcrumbList","@id":"https:\/\/www.aamirharoon.com\/?p=144#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/aamirharoon.com\/"},{"@type":"ListItem","position":2,"name":"Variables: Shell, SQL*PLUS and PL\/SQL"}]},{"@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\/144","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=144"}],"version-history":[{"count":24,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/144\/revisions"}],"predecessor-version":[{"id":181,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/posts\/144\/revisions\/181"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=\/wp\/v2\/media\/165"}],"wp:attachment":[{"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aamirharoon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}