Re: Open twice (or more) the same cursor

the cursor can only be opened once, but you could do a recursive procedure call, ie.:

declare

    procedure findC (...

        cursor cFindC ...
    is
    ...

• found record, recursive call findC(...); ... end; begin
  • initial call findC(...); end;

(or create the recursively called procedure as a stored procedure)

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Bernard Drolet" <bernard_at_drolet.com> wrote in message
news:a9ccc9f9.0311022000.2ce6af4e_at_posting.google.com...


> 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