{"id":1345,"date":"2012-03-09T04:41:23","date_gmt":"2012-03-09T09:41:23","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1345"},"modified":"2012-03-09T04:41:23","modified_gmt":"2012-03-09T09:41:23","slug":"get-all-the-variables-in-an-oracle-package-procedure-or-function","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/","title":{"rendered":"Get all the variables in an Oracle Package, Procedure or Function"},"content":{"rendered":"

This demo illustrates how to use general sql parser<\/a> to get all the variables in an Oracle Package, Procedure or Function that are in scope based on a line number. <\/p>\n

For example, if we specify a line number that is within a specific procedure in a package, this demo will let you know which variables are defined for that procedure, not any others. The same for stand alone procedures and functions that are not part of a package.<\/p>\n

Let’s take this SQL for example:<\/p>\n

\r\n01  create or replace\r\n02  package body pkg_dbgd as\r\n03    function tst_1(i in integer) return integer is\r\n04    begin\r\n05      if i between 5 and 10 then\r\n06        return 2*i;\r\n07      end if;\r\n08  \r\n09      if i between 0 and 4 then\r\n10        return tst_2(3+i);\r\n11      end if;\r\n12  \r\n13      if i between 11 and 15 then\r\n14        return tst_2(i-2);\r\n15      end if;\r\n16  \r\n17      return i;\r\n18    end tst_1;\r\n19  \r\n20    function tst_2(var1 in integer) return integer as\r\n21  \tmyreturn integer;\r\n22    begin\r\n23  \t  myreturn := var1-1;\r\n24    \r\n25      if var1 between 6 and 8 then\r\n26        myreturn := fn_add_numbers(var1,10);\r\n27      end if;\r\n28  \r\n29      if var1 between 1 and 5 then\r\n30        myreturn := fn_add_numbers(var1,5);\r\n31      end if;\r\n32  \t\r\n33      return myreturn;\r\n34    end tst_2;\r\n35  end pkg_dbgd\r\n<\/pre>\n
Lets say which variables would be available in scope at line 25,
\nthis demo will show you  myreturn (Integer) and var1 (integer).
\nthat would be all the variables for the function tst_2. <\/p>\n
Download this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
This demo illustrates how to use general sql parser to get all the variables in an Oracle Package, Procedure or Function that are in scope based on a line number. For example, if we specify a line number that is within a specific procedure in a package, this demo will let you know which variables are defined for that procedure, not any others. The same for stand alone procedures and functions that are not part of a package. Let’s take this SQL for example: 01 create or replace 02 package body pkg_dbgd as 03 function tst_1(i in integer) return integer\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Get all the variables in an Oracle Package, Procedure or Function\" \/>\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.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Get all the variables in an Oracle Package, Procedure or Function\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2012-03-09T09:41:23+00:00\",\"dateModified\":\"2012-03-09T09:41:23+00:00\",\"description\":\"Get all the variables in an Oracle Package, Procedure or Function\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Get all the variables in an Oracle Package, Procedure or Function\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Get all the variables in an Oracle Package, Procedure or Function","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.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Get all the variables in an Oracle Package, Procedure or Function","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/","og_site_name":"SQL and Data Blog","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2012-03-09T09:41:23+00:00","dateModified":"2012-03-09T09:41:23+00:00","description":"Get all the variables in an Oracle Package, Procedure or Function","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-all-the-variables-in-an-oracle-package-procedure-or-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Get all the variables in an Oracle Package, Procedure or Function"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1345"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1345"}],"version-history":[{"count":3,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1345\/revisions"}],"predecessor-version":[{"id":1348,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/1345\/revisions\/1348"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}