If we knew what we were doing, it wouldn't be research.

We all have our side projects.  Some are as big as the normal projects we work on professionally, while others can be that little function that is elusive.  In some ways it's that little thing that itches us time and again. Yet, we scratch that itch to get a breather and sort of recharge the creative side of our work.

For some time now there have been a number of places where I had been instrumenting code for anything from performance to functional issues and I had thought (like several before me) that it would be nice to tell what program was running when the log was created.  Not surprising that Oracle has a couple of items that can help

• $$PLSQL_UNIT - Will tell you what package, procedure, function, or object method your log came from, it won't tell you what program within the package has executed.
• $$PLSQL_LINE - Tells you at what line the value, or log in my case, was recorded

What I was after is that subprogram reference inside of the package and I did some research and found a lot of code.  Not all of it is super performant, and some of it just does not work.

For methods that others have used or expressed here is a short list of references:

• https://community.oracle.com/thread/1042794?tstart=0
• https://community.oracle.com/thread/1051771?tstart=0
• https://community.oracle.com/thread/2487174?tstart=0

After these and several other attempts I decided that I should look into it myself and find a better way. and believe me.

(Demodivator copyright © www.despair.com)

In various attempts I found that my code was as big or slow as the other examples.  I'm a big fan of the Data Dictionary, and this is where most of my attempts were focused.  There are great resources in those views.

•  ALL_PROCEDURES - Gives a full list of public procedures, functions and those sub-programs in a package.
• ALL_ARGUEMENTS - Give a full list of the parameters for anything found in ALL_PROCEDURES
• ALL_IDENTIFIERS - If the previous views are a gold mine of information this one became the mother-lode.  It shows a variety of information on the package, beyond what is public.  If you spend some time and learn how to read and parse the data there is so much you can understand and then do.

I won't go into the all the attempts to get this information but, instead will simply show the best solution I have to date.  It's not perfect but it is fast and mostly accurate.

create or replace function f_get_package_program
(p_plsql_unit in varchar2
,p_plsql_line in number)
return varchar2
as
  l_retvar varchar2(1000);
begin
 
  select  ident
  into    l_retvar
  from(
    SELECT  a.OBJECT_NAME||'.'||a.NAME||':'||LINE ident
    from    SYS.ALL_IDENTIFIERS a
    where   object_type = 'PACKAGE BODY'
      and   object_name = p_plsql_unit
      and   type in ('PACKAGE','PROCEDURE','FUNCTION')
      and   usage = 'DEFINITION'
      and   line <= p_plsql_line
    order by line desc)
  where rownum = 1;
 
  return l_retvar;

end f_get_package_program;


Suffice it to say, wandering in the forest sometimes reveals a much shorter path

Cheers!