Performance of embedded cursors???

Hi,

I have been writing a stored procedure and am trying to use embedded cursors (3 level of nesting) for the first time. My immediate concern is that, now the data set is small but it is expected to explode and grow very big in future. All I want to know is that if some one has used embedded cursors, please shed some light on possible performance bottlenecks/issues ..

More over I am using 'start with' and 'connect by' clause in these embedded cursors .. the query is going to look some what like

 WQ_QUERY := 'select C.claim_exposure_id, cursor(select level, A.folder_id, A.folder_name, A.parent_folder_id, '||
'cursor(select level, document_id, parent_document_id, descriptor from
clm_document B where b.folder_id = a.folder_id '||
'start with parent_document_id is null connect by prior document_id =
parent_document_id '||
') X from clm_folder A where A.claim_id = C.Claim_Id '||
'start with a.parent_folder_id is null connect by prior a.folder_id =
a.parent_folder_id) Y '||
'from clm_claim_exposure C where claim_id = :1';

 open retcur for WQ_QUERY using in_claim_number;

Please help..

Thanks :) Received on Fri Sep 16 2005 - 15:49:30 CDT