Re: Software to Generate PL/SQL Call Stack??

rajbrown_at_gmail.com wrote:
: Hello - I might be living in cloud cuckoo land, but I'm wondering if
: there's any way to generate documentation on a PL/SQL package which
: will display the full call stack. I imagine it would need some 3rd
: party software to parse the source code to display it, but I could be
: wrong. Ideally I'd like something generated such as this:

: p_process
: -> f_get_name
: -> f_validate
: -> p_do_some_stuff
: -> p_get_going
: -> p_insert_some_records
: -> p_delete_everything
: -> f_check_nothing exists

It is a very common desire of many of us (in many languages, not just PL/SQL), to be able to extract this structure of the code.

I am always amazed at how hard it can be to get this information.

In a detailed conversation, a number of issues and problems that would arise while trying to generate this structure would be mentioned, to which I would respond with the 90% adage.

One of those "problems" (see above) that would deserve mentioning that would arise in PL/SQL/Oracle is that a general purpose tool would want to look inside parts of the application such as forms or reports that are not easily traceable. (Have you ever looked for a SQL bug in the WHERE clause of a data block in a form?)

There are tools for some languages, for example I have used free tools that do this for old style C programs, and a demo of a commercial product that does this for new C programs, so perhaps there is something for pl/sql if you look hard enough.

I have toyed with adding syntax using macros or comments to do this in other settings, and that, if used consistently, might be useful, but it can break down easily since not enforced by any compiler. (just like when using javadoc, or "plsqldoc", which is a tool one should consider using, in my opinion, by the way).

e.g. in C you can do thing like

	#define CALL
	#define DECLARE

	DECLARE int outer_function( int x )
	{
	  int i;
	  i = CALL my_function(

and later you very easily parse (i.e. using perl) the source by looking for the DECLARE and CALL keywords.

I haven't done that in pl/sql, but I'm sure you could come up with something similar easily enough, using some combination of comments /*#DECLARE*/ /*#CALL*/ etc and/or sql*plus DEFINE's.

$0.10 Received on Sun Jan 22 2006 - 15:35:57 CST