Thanks for the question, Huberto.

Asked: November 14, 2000 - 5:30 am UTC

Last updated: March 25, 2013 - 9:55 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi,

I want to determine the name of a function/procedure within a package. Via the callstack it is possible to determine the owner and name of the package, but how can I retrieve information on what specific function/procedure it is?

Best regards,

Huberto

and Tom said...

You would have to take the line number given to you by the who_called_me function and do something like:

select text
from all_source
where ( upper(text) like '%PROCEDURE%'
or
upper(text) like '%FUNCTION%' )
and line < :that_line_number
and owner = :that_owner
and name = :that_name
order by line desc

that is - you have to read the code to find that name. The function names within a package body are not maintained in the call stack (or anywhere).

A query like the above will be an 80% solution (not a full solution). It will fail in some cases, for example:

...
procedure real_procedure_here
is
l_variables ...;

procedure hidden_procedure_here
is
begin
null;
end;
begin
call_procedure_here;
end;

procedure
another_real_procedure_here
is
begin
call_procedure_here;
end;
....

If call_procedure_here was to try and find the name of the procedure in the packge that called it -- it would fail using my query above. In the first case, it would find the hidden, local procedure. In the second case, it would find "blank" since the procedure name does not appear on the same line as the procedure keyword itself.

If you use neither of these constructs AND you have access to the bodies of all code -- this might work for you (but will be pretty slow).

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

great answer - but is it still true?

matthias, October 29, 2009 - 3:33 am UTC

Hello Tom,

I'm tuning our instrumentation and found that very part is truely "slow". We already use collections to cache the information from all_source, but there's still a huge overhead because every function name has to be retrieved once per session.

Do you know of a better / faster solution with 10g/11g?

Or do I have to go materialized?

Thanks...

October 29, 2009 - 8:42 am UTC

sure, easy

stop saving the function name, get it at report time, just save the line number.

The instrumented data only needs the bits that are needed. the function/procedure name - it is obviously not needed in the instrumentation trace since you can extract it (if you need it, I've never needed it myself, having the line numbers is so much more useful, you can get everything else from that).

the fastest way to do something is to not do it or do it later.

do it later.

ack

matthias, November 05, 2009 - 4:53 pm UTC

> ...or do it later

sorry, I didn't remember that part of the mantra ;)

but ok. I'll turn off the function names in production and keep them for testing and development.

I'd just hoped there was some _$ view from the compiler, where I could see the line numbers.

+++ wouldn't it be great to have the ~body line numbers in all_procedures?

BTW... I'm curious why all_procedures isn't restricted to objects I have access to (10.2)? So that would be better than with all_source...

cheers

November 11, 2009 - 1:22 pm UTC

I don't know what you mean by all_procedures not being restricted? It is.

you have access to the line number - format call stack gives it to you.

you also have compile time 'macros' that give this to you

ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_output.put_line( 'this is line ' || $$PLSQL_LINE || ' of plsql unit ' || $$PLSQL_UNIT );
  5
  6          null;
  7
  8          dbms_output.put_line( 'this is line ' || $$PLSQL_LINE || ' of plsql unit ' || $$PLSQL_UNIT );
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA11GR2> exec p
this is line 4 of plsql unit P
this is line 8 of plsql unit P

PL/SQL procedure successfully completed.

I assume you were doing packages - seems reasonable to assume that - because you would get the line number and function name right out of format call stack at the worst or using the above at the best.

Just save the unit and line number - you get the function name anytime you want later.

Line Numbers Change

Yuan, December 07, 2012 - 10:28 am UTC

Say you want to see if a particular procedure in a package is no longer being called and thus can be dropped. Auditing by logging PLSQL_UNIT and line number and querying that is not very convenient as you'd have to join to all_source and have privileges to see all the sources. Further, it is unreliable as the source changes. I've never tried auditing an object, but I'd assume that in this case it would only tell you when the package was being called, not the specific procedure in that package.

December 14, 2012 - 2:13 pm UTC

frankly, anyone that says "we can drop X because it appears no one has called/selected/touched/whatever X for N units of time" is someone I am very suspicious of.

If you have code you

a) don't know what it does
b) for whom it does it
c) don't know if it is even used anymore

you have a serious code problem. No level of auditing would be useful here - this is like people looking for tables that haven't been touched in N units of time so they can "drop them". I don't know what sort of environment exists that people would actually do that (I know, it happens - they do, I just cannot fundamentally understand how anyone could get to such a point - how do you manage the code in the first place...)

Code Management

Yuan, March 15, 2013 - 9:55 am UTC

Do you have a book or something that talks about managing code in such a way that it is known where every since procedure/function/table/etc as applications evolve around your database? I would find that extremely useful.

March 25, 2013 - 2:54 pm UTC

I have not personally written a book on configuration management, however there are hundreds of such books out there.

software configuration management patterns

typed into amazon turned up a ton of likely prospects.

Code Management (Correction)

Yuan, March 15, 2013 - 10:14 am UTC

Sorry, I left something out of my prior post.

Do you have a book or something that talks about managing code in such a way that it is known where
every since procedure/function/table/etc is used as applications evolve around your database? I would find
that extremely useful.

March 25, 2013 - 9:55 am UTC

see above.

we can only track static dependencies - so if you use PLSQL and you use static (not execute immediate, not dbms_sql) we can do that for you.

otherwise, you have to do it in your documentation.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

great answer - but is it still true?

ack

Line Numbers Change

Code Management

Code Management (Correction)

More to Explore

PL/SQL demos

PL/SQL docs

Who Am I -> determine the procedure/function within a package

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

great answer - but is it still true?

ack

Line Numbers Change

Code Management

Code Management (Correction)

More to Explore

PL/SQL demos

PL/SQL docs