Open twice (or more) the same cursor

[Bernard Drolet]

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

[mcstock]

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
[phone]

"Bernard Drolet" <be*****@drolet.com> wrote in message
news:a9**************************@posting.google.c om...

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