{"id":475,"date":"2010-06-27T12:12:57","date_gmt":"2010-06-27T11:12:57","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=475"},"modified":"2010-06-27T12:12:57","modified_gmt":"2010-06-27T11:12:57","slug":"where-am-i%e2%80%a6","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=475","title":{"rendered":"Where Am I\u2026"},"content":{"rendered":"<style type=\"text\/css\">\n<p>.code, .code pre\n{\n\tfont-size: small;\n\tcolor: #00007F;\n\tfont-family: \"Courier New\"\n                    Courier\n                    monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #008000; }\n.code .kwrd { color: #007F7F; }\n.code .comm { color: #ff0000; }\n.code .str { color: #006080; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<p align=\"left\">On my new job we wanted to provide some logging. Especially about what program is currently running. Of course it would be easy enough to add a line at the start of the program with the name of that program and one at the end. But, as things go during development, names (and types) of programs tend to change and we would have to change those lines accordingly. Something that can (and will) be easily forgotten.<\/p>\n<p align=\"left\">Why not have the code tell us where we are. That way we wouldn\u2019t have to change our code if the program name changes. But unfortunately there is no such thing as a function to tell me where I am.<\/p>\n<p><!--more--><\/p>\n<p align=\"left\">So, I decided to write one of my own. In the old days, using Novell network software, there was a function called <strong>WhoAmI<\/strong>, so I decided to name my function <strong>WhereAmI<\/strong>. What does <a href=\"http:\/\/www.oracle.com\" target=\"_blank\">Oracle<\/a> offer me to tell me where I am. There is such a thing as <strong>DBMS_UTILITY.FORMAT_CALL_STACK<\/strong>. This provides a nice call stack of all the programs called in sequence like this:<\/p>\n<div class=\"code\">\n<p><pre class=\"alt\"><span class=\"lnum\">   1:  <\/span>----- PL\/SQL Call Stack -----<\/pre>\n<pre><span class=\"lnum\">   2:  <\/span>  object      line  object<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>  handle    number  name<\/pre>\n<pre><span class=\"lnum\">   4:  <\/span>287562CC         7  function UTIL.WHEREAMI<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>29DEBFF8         7  anonymous block<\/pre>\n<\/p>\n<\/div>\n<p>The first three line are header information. The fourth line is where we are when this function is being called, being our function, so the first line of interest to us is the fifth line:<\/p>\n<div class=\"code\">\n<p><pre class=\"alt\"><span class=\"lnum\">   1:  <\/span>29DEBFF8         7  anonymous block<\/pre>\n<\/p>\n<\/div>\n<p>Actually we only need the text in the third column. That tells us exactly where we are. Using string searching capabilities we remove everything we don\u2019t need. First of all, we search for the position of the first space, then we copy the rest of the string and by using the trim functionality we remove any (leading and trailing) spaces from it. Now we have:<\/p>\n<div class=\"code\">\n<p><pre class=\"alt\"><span class=\"lnum\">   1:  <\/span>7  anonymous block<\/pre>\n<\/p>\n<\/div>\n<p>If we execute this trick again we are left with the information we are interested in:<\/p>\n<div class=\"code\">\n<p><pre class=\"alt\"><span class=\"lnum\">   1:  <\/span>anonymous block<\/pre>\n<\/p>\n<\/div>\n<p>Using this function at the start and end of our programs where to see the exact name of the program we are entering or exiting no matter what the type is, or if it is packaged or not. Only thing it doesn\u2019t show me is whether it\u2019s a private program or a local program.<\/p>\n<p>The complete code for the function is like this:<\/p>\n<div class=\"code\">\n<p><pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"kwrd\">FUNCTION<\/span> whereami <span class=\"kwrd\">RETURN VARCHAR2 IS<\/span><\/pre>\n<pre><span class=\"lnum\">   2:  <\/span>  l_callstack   <span class=\"kwrd\">VARCHAR2<\/span>(2000);<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>  l_returnvalue <span class=\"kwrd\">VARCHAR2<\/span>(100);<\/pre>\n<pre><span class=\"lnum\">   4:  <\/span><span class=\"kwrd\">BEGIN<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>    &lt;&lt;code&gt;&gt;<\/pre>\n<pre><span class=\"lnum\">   6  <\/span>  <span class=\"comm\">-- Get the current callstack<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   7:  <\/span>  l_callstack := dbms_utility.format_call_stack;<\/pre>\n<pre><span class=\"lnum\">   8:  <\/span> <span class=\"comm\">-- The line of interest is the fourth line.<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">   9:  <\/span>  l_returnvalue := substr(l_callstack<\/pre>\n<pre><span class=\"lnum\">  10:  <\/span>                         ,instr(l_callstack, chr(10), 1, 4) + 1<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  11:  <\/span>                         ,(instr(l_callstack, chr(10), 1, 5) - instr(l_callstack, chr(10), 1, 4)));<\/pre>\n<pre><span class=\"lnum\">  12:  <\/span>  <span class=\"comm\">-- All we are interested in is the type and the name of the program.<\/span><\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  13:  <\/span>  l_returnvalue := <span class=\"kwrd\">TRIM<\/span>(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));<\/pre>\n<pre><span class=\"lnum\">  14  <\/span>   l_returnvalue := <span class=\"kwrd\">TRIM<\/span>(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));<\/pre>\n<pre class=\"alt\"><span class=\"lnum\">  15:  <\/span>  <span class=\"kwrd\">RETURN<\/span> l_returnvalue;<\/pre>\n<pre><span class=\"lnum\">  16  <\/span><span class=\"kwrd\">END<\/span> whereami;<\/pre>\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>On my new job we wanted to provide some logging. Especially about what program is currently running. Of course it would be easy enough to add a line at the start of the program with the name of that program and one at the end. But, as things go during development, names (and types) of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-475","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=475"}],"version-history":[{"count":1,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions"}],"predecessor-version":[{"id":476,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions\/476"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}