Re: How to trace what is happening inside the stored procedure

Tim Gorman apparently said,on my timestamp of 7/02/2005 2:29 PM:

> But if you're in the habit of building apps with mile-long unique/primary
> key values, then I hope I never have to maintain or replicate from
> applications you've designed or built.

Only when I have to deal with Java developers and their OIDs. They loooove long identifiers...
:D

>>32 characters.  If I want to note the name of the procedure that is
>>executing in a package plus a running counter, I'm already over the limit:
>>a name of a procedure is already by itself 30 characters long max.

This is precisely one of the best uses of set_action, IMHO: I want to follow what is happening inside a stored proc, how far it has progressed, etcetc.

> Besides, wouldn't you be better off using the SET_SESSION_LONGOPS procedure
> in the DBMS_APPLICATION_INFO package to put that type of stuff (i.e.
> counters, progress, etc) to the V$SESSION_LONGOPS view? MODULE, ACTION, and
> CLIENT_INFO are not the only option for that type of information...

Very good point. I thought of doing that. But to me, longops would be something I'd associate say with a large cursor or discrete SQL that takes a significant time to execute. It seems to be the way it is used by default by Oracle: FTS, sort/merges, index creations, and so on. I haven't yet seen a single case of a SP being referenced in longops.

In a SP I'd rather have a general overview of how far it has progressed. That may involve just the given procedure name and how many times it was called/executed. No long ops anywhere. Just a long process that calls a given routine a large number of times. That's how I use it anyways. And sometimes I have to go through hoops with the long
procedure_names_that_everyone_seems_to_favor_nowadays. RPITA...
> You feel that the DBMS_APPLICATION_INFO package is well documented?

Perish the thought!
;)

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l