.code, .code pre
{
font-size: small;
color: #00007F;
font-family: “Courier New”
Courier
monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.code pre { margin: 0em; }
.code .rem { color: #008000; }
.code .kwrd { color: #007F7F; }
.code .comm { color: #ff0000; }
.code .str { color: #006080; }
.code .op { color: #0000c0; }
.code .preproc { color: #cc6633; }
.code .asp { background-color: #ffff00; }
.code .html { color: #800000; }
.code .attr { color: #ff0000; }
.code .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.code .lnum { color: #606060; }
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.
Why not have the code tell us where we are. That way we wouldn’t 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.
So, I decided to write one of my own. In the old days, using Novell network software, there was a function called WhoAmI, so I decided to name my function WhereAmI. What does Oracle offer me to tell me where I am. There is such a thing as DBMS_UTILITY.FORMAT_CALL_STACK. This provides a nice call stack of all the programs called in sequence like this:
1: ----- PL/SQL Call Stack -----
2: object line object
3: handle number name
4: 287562CC 7 function UTIL.WHEREAMI
5: 29DEBFF8 7 anonymous block
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:
1: 29DEBFF8 7 anonymous block
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’t 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:
If we execute this trick again we are left with the information we are interested in:
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’t show me is whether it’s a private program or a local program.
The complete code for the function is like this:
1: FUNCTION whereami RETURN VARCHAR2 IS
2: l_callstack VARCHAR2(2000);
3: l_returnvalue VARCHAR2(100);
4: BEGIN
5: <<code>>
6 -- Get the current callstack
7: l_callstack := dbms_utility.format_call_stack;
8: -- The line of interest is the fourth line.
9: l_returnvalue := substr(l_callstack
10: ,instr(l_callstack, chr(10), 1, 4) + 1
11: ,(instr(l_callstack, chr(10), 1, 5) - instr(l_callstack, chr(10), 1, 4)));
12: -- All we are interested in is the type and the name of the program.
13: l_returnvalue := TRIM(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));
14 l_returnvalue := TRIM(substr(l_returnvalue, instr(l_returnvalue, ' ', 1)));
15: RETURN l_returnvalue;
16 END whereami;
