I gather that the SQL within functions that are called in top-level SQL statement are not considered recursive, and therefore do not conform to Cary's rule
"A database call with dep=n+1 is the recursive child of the first subsequent dep=n database call listed in the SQL trace data stream." But the "dep" value for such SQL is indeed 1 more than the "dep" value of the caller, as in this excerpt: PARSING IN CURSOR #1 len=124 dep=0 uid=149 oct=3 lid=149 tim=2988575045 hv=440952914 ad='3775ee70' select outstanding_vested_for_100K(hextoraw('0001E8A6F8A94B40B95BBE32AF120D95'), to_date('28-AUG-03','DD-MON-YY')) from dual END OF STMT PARSE #1:c=0,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2988540123 EXEC #1:c=0,e=396,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=2988596994 WAIT #1: nam='SQL*Net message to client' ela= 10 p1=1413697536 p2=1 p3=0 ===================== PARSING IN CURSOR #12 len=365 dep=1 uid=149 oct=3 lid=149 tim=2988604429 hv=3990937121 ad='344ddb98' SELECT GrantType.Behavior from GrantType,PlanStockClassGrantType,IssueGrant,GrantParticipant where GrantParticipant.GrantParticipant_pk=:b1 and IssueGrant.IssueGrant_pk=GrantParticipant.IssueGrant_fk and IssueGrant.PlanStockClassGrantType_fk = PlanStockClassGrantType.PlanStockClassGrantType_pk and PlanStockClassGrantType.GrantType_fk=GrantType.GrantType_pk The first SQL is what I entered in SQL*Plus (immediately after turning on 10046 tracing). The second is contained in the outstanding_vested_for_100k function. Why is the motivating SQL shown in the trace first? Is the rule for determining "recursive" relationships in a case like this simply to follow the increasing "dep" values (with detours for "true" recursive SQL, such as data dictionary access, that follows Cary's rule)? TIA ===== Paul Baumgartel Transcentive, Inc. www.transcentive.com __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).