Open twice (or more) the same cursor

Hi, I have a recursive table "Objectnew" with the columns "objectref" (primary key), objectname, objectid, and parentobjectid Recursive Relation: objectid ----< Parentobjectid

I have the following cursor that extracts information about all objects with a specific name below a starting point

DECLARE
    CURSOR findChildren(

                starting Objectnew.objectref%TYPE, 
                name Objectnew.objectname%TYPE) IS
          SELECT 
              objectref,
              objectname
          FROM
              Objectnew
          WHERE
              objectref in (
                   SELECT
                        objectref
                   FROM
                        Objectnew 
                   CONNECT BY PRIOR objectid = parentobjectid
                   START WITH objectref = startingref
              ) AND
              objectname = name;

What I need is, once I found some objects with a specific name, to run another query below each of them to find objects with another name.

Something like
OPEN findChildren(1234, 'A');
LOOP

     FETCH findChildren INTO ref, name
     EXIT WHEN findChildren%NOTFOUND;



     -- I have found an object A, 


     -- I need to know if there is a B below A


     OPEN findChildren(ref, 'B');
     LOOP
          FETCH findChildren INTO ref2, name2
          EXIT WHEN findChildren%NOTFOUND;

          -- YES, there is a B, process it
...
                   

Is it possible without creating a duplicate of cursor findChildren ?

Thank you

Bernard Drolet Received on Mon Nov 03 2003 - 05:00:22 CET