Home » SQL & PL/SQL » SQL & PL/SQL » procs calling function
procs calling function [message #390492] Fri, 06 March 2009 10:25 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a function A inside a package, how to find from the database which other
packages / procedures / functions are calling function A.
Re: procs calling function [message #390494 is a reply to message #390492] Fri, 06 March 2009 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way unless anylzing the code... but you should know which function calls which procedure, isn't it specified?

Regards
Michel
Re: procs calling function [message #390496 is a reply to message #390492] Fri, 06 March 2009 10:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot find who calls your packaged function, but you can see who calls your package (using the %_dependencies views). Then it's a manual second step to see which procedure/function within the package is called.
Re: procs calling function [message #390503 is a reply to message #390492] Fri, 06 March 2009 12:37 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
how about this"

select * from dba_source where text like '%funA%'


I think this is correct
Re: procs calling function [message #390504 is a reply to message #390503] Fri, 06 March 2009 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What if there is a variable named funA?
What if there is another thing named XfunA?
What if one call the funtion FUNA or funa or Funa...?
What if...

Regards
Michel
Re: procs calling function [message #390520 is a reply to message #390504] Fri, 06 March 2009 23:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Ah... we pine for the old days. I remember building documentation for COBOL programs. On piece of it was a call tree. For this I figured out that the cobol compiler had flags that when supplied would cause the compiler to dump a token map. The map gave the line number and byte position of every token reference. Program unit names are of course tokens as well. So you got the location each was defined at, and where every call to them was made. A little logic later and you had a call tree.

I supposed plsql might have a similar utility. But there is no easy access to it from the developer side.

Maybe someone knows of plsql flags or hidden paramters or some such thing that might provide the necessary dump?

This has been a long sought after item... to have the database produce decent documentation of what is loaded into it. Oh well, Oracle listens, maybe someday they will give it to us.

Kevin
Re: procs calling function [message #390578 is a reply to message #390492] Sat, 07 March 2009 14:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It may help:

http://asktom.oracle.com/tkyte/who_called_me/index.html

[Updated on: Sat, 07 March 2009 14:25]

Report message to a moderator

Re: procs calling function [message #390579 is a reply to message #390578] Sat, 07 March 2009 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given the question I don't think it can help.

Regards
Michel
Re: procs calling function [message #390606 is a reply to message #390579] Sun, 08 March 2009 10:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are willing to spend some money, there are third party tools that do this job for you. Quest had one I think, or maybe it was someone else. They are expensive though. Its not like you could shell out 200 bucks of your own cash in order to give yourself a tool that puts you ahead of the rest of the fools you work with. These tools usually are a big time investment by a department or larger group that is real serious about doing code analysis.

I think it might have been called SQL_ANALYZER, not sure.

Then there is VISUSTIN. This is a cool tool. It takes all sorts of code and backwards engineers a flowchart. OK, some people might think flowcharts are old school but they have many uses even today. I would not code from them, but I do use them as one part of my documentation. It does plsql as long as the plsql is not way big in number of lines. It does not however jump across packages. Its cheap though, < 400$, and focused on what it does so it does it pretty well.

As regards other comments in this thread, I would point out that depending upon what you intend to do with the information, a 100% solution is not always required. A 99% solution would be awesome, and we could live or find hacks around that 1% where a variable was the same as a procedure name somewhere else, or reserved words were being used etc. From a documentation perspective, getting 99% of the way there is wicked farther along than we have today. Maybe someone would like to write us all a quick partial solution?

I hope one day Oracle wakes up and figures out they are missing the boat on helping us manage our code, because they do not give use automatic documentation. They should be generating call trees, flow charts, sql extract w/plans, crud matricies runtime stats, and error logs in one nice neat place rather than making us work to get it ourselves. Then again, maybe I haven't seen the latest stuff Oracle has dumped into Enterprise Manager...

Ah well, if our jobs were easy, everyone would do them.

Good luck, Kevin
Re: procs calling function [message #390851 is a reply to message #390496] Tue, 10 March 2009 01:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As Frank said
Quote:
You cannot find who calls your packaged function, but you can see who calls your package (using the %_dependencies views). Then it's a manual second step to see which procedure/function within the package is called.
.

You can use

select name||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE
from user_dependencies
where lower(REFERENCED_NAME) like 'Your function Name' and REFERENCED_TYPE like 'FUNCTION';

[Updated on: Tue, 10 March 2009 01:04]

Report message to a moderator

Re: procs calling function [message #390859 is a reply to message #390851] Tue, 10 March 2009 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramoradba wrote on Tue, 10 March 2009 07:00
As Frank said
Quote:
You cannot find who calls your packaged function, but you can see who calls your package (using the %_dependencies views). Then it's a manual second step to see which procedure/function within the package is called.
.

You can use

select name||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE
from user_dependencies
where lower(REFERENCED_NAME) like 'Your function Name' and REFERENCED_TYPE like 'FUNCTION';


Reread what I posted and what you quoted.
You cannot search for "your function name", you can only search for the package containing your function!
Re: procs calling function [message #390876 is a reply to message #390859] Tue, 10 March 2009 02:42 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Frank,you said that
Quote:
Reread what I posted and what you quoted.
You cannot search for "your function name", you can only search for the package containing your function!


see this once

 select name||'-->'||type||'-->'||REFERENCED_NAME||'-->'||REFERENCED_TYPE
 from user_dependencies
 where lower(REFERENCED_NAME) like 'todate' and REFERENCED_TYPE like 'FUNCTION'


CALCULATE_COUNTS-->PROCEDURE-->TODATE-->FUNCTION
SP_POPULATE_CPR-->PROCEDURE-->TODATE-->FUNCTION
COMPARE-->PROCEDURE-->TODATE-->FUNCTION
MEMBERSHIP_STATISTICS_UPD-->PACKAGE BODY-->TODATE-->FUNCTION
STATS_UPDATE_NEW-->PACKAGE BODY-->TODATE-->FUNCTION
OBSOLETE_DATA_UPD-->PACKAGE BODY-->TODATE-->FUNCTION
TEMP_STATS_UPDATE_NEW-->PACKAGE BODY-->TODATE-->FUNCTION
CALCULATE_COUNTS_TEMP-->PROCEDURE-->TODATE-->FUNCTION



here procedure 'COMPARE' is calling 'todate' function.
when ever the procedure executed, internally it will call todate function. and if you use dba_dependencies,
you can select the owner,referenced_owner columns also...

Quote:
ora1980

I have a function A inside a package, how to find from the database which other
packages / procedures / functions are calling function A.


@ora1980 required i think this is the solution.

thanks & regards

Sriram.
Re: procs calling function [message #390878 is a reply to message #390876] Tue, 10 March 2009 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If TODATE is inside a package you CANNOT see who is calling it with %dependencies views.
Carefully note:
Quote:
I have a function A inside a package


Regards
Michel

[Updated on: Tue, 10 March 2009 02:49]

Report message to a moderator

Re: procs calling function [message #390925 is a reply to message #390878] Tue, 10 March 2009 06:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks Michel.
first of all i agree with you. but please see this ....

 create or replace package mohpack
 as
 function todate(ts in number) return date;
 end;
 /
Package created.


IND> select name from user_source
  2  where lower(text) like '%function%todate%' and type like 'PACKAGE';
MOHPACK

1 row selected.

this is just an extension for @ora1980.

thanks & regards
Sriram.
Re: procs calling function [message #390926 is a reply to message #390925] Tue, 10 March 2009 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I already answered to this:
Michel Cadot wrote on Fri, 06 March 2009 19:45
What if there is a variable named funA?
What if there is another thing named XfunA?
What if one call the funtion FUNA or funa or Funa...?
What if...

Regards
Michel

Re: procs calling function [message #390932 is a reply to message #390926] Tue, 10 March 2009 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm not convined that those are significant problems

You can see all the calls to your packaged function from outside it's own package by looking for <Package_name>.<function_name> in user_source.

As for calls inside the package, you can't have a package function with the same name as a package variable, and as oracle object names are case insensitive, you'll have trouble having two functions in the same package with names that differ only in case.
Re: procs calling function [message #390937 is a reply to message #390932] Tue, 10 March 2009 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can see all the calls to your packaged function from outside it's own package by looking for <Package_name>.<function_name> in user_source.

Not quite true:
SQL> create or replace package pkg is function fct return integer; end;
  2  /

Package created.

SQL> create or replace package body pkg is function fct return integer is begin return 1; end; end;
  2  /

Package body created.

SQL> create or replace procedure p is i integer; begin i := pkg
  2     . -- Now I call fct function
  3  fct; end;
  4  /

Procedure created.

Regards
Michel

[Updated on: Tue, 10 March 2009 06:46]

Report message to a moderator

Re: procs calling function [message #390943 is a reply to message #390926] Tue, 10 March 2009 07:06 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
IND> select name from user_source
  2  where lower(text) like '%function todate%' and type like 'PACKAGE';
MOHPACK

1 row selected.


Please see the code
1) iam searching only for the type 'PACKAGE'
2) iam specifying the text exactly
so i think this will solve for the type PACKAGE.

we cant find out the functions which are in a package why because those are created like object_name as " standard" in sys schema. please let me know if iam wrong.
thanks & regards
Sriram

and
IND>select name from user_source
  2   where lower(text) like '%function fct%' and type like 'PACKAGE';
PKG

1 row selected.
Re: procs calling function [message #390954 is a reply to message #390937] Tue, 10 March 2009 07:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michel: Very neat! That'd never occurred to me.

I'd have a developer who actually did that impaled and left in the department as a warning to the others, but your point still stands.
Previous Topic: How to delete duplicate entries in a table ?
Next Topic: ORA-04091: table is mutating ... how to?
Goto Forum:
  


Current Time: Wed Dec 07 06:59:32 CST 2016

Total time taken to generate the page: 0.20926 seconds