Populate table type from CUrsor

From: Björn-Åke Segrell <pinne_at_pegit.se>
Date: Thu, 02 Dec 1999 14:44:02 +0100
Message-ID: <384677A2.3B13938A@pegit.se>

Hi,

Oracle 8.0.5 on Linux and Solaris.
I'm trying to populate a table defined as the row type from a cursor with the values from that same
cursor.
But i can't get it to work. I'm still trying, but was thinking someone else migt have the answer.
See #Test2 below

Very grateful for any help on this matter.

/CHEERS Example
###########>

DROP TABLE TOC; CREATE TABLE Toc(toc_id NUMBER NOT NULL, prevtoc_id NUMBER, ordnnr NUMBER, name VARCHAR2(32) NOT NULL);

INSERT INTO Toc VALUES(1, NULL, NULL, 'TocTop 1');
INSERT INTO Toc VALUES(2, NULL, NULL, 'TocTop 2');
INSERT INTO Toc VALUES(3, NULL, NULL, 'TocTop 3');
INSERT INTO Toc VALUES(4, NULL, NULL, 'TocTop 4');
INSERT INTO Toc VALUES(5, 1, NULL, 'Tocchild 1-1');
INSERT INTO Toc VALUES(6, 1, NULL, 'Tocchild 1-2');
INSERT INTO Toc VALUES(7, 5, NULL, 'Tocchild 5-1');
INSERT INTO Toc VALUES(8, 5, NULL, 'Tocchild 5-2');
INSERT INTO Toc VALUES(9, 7, NULL, 'Tocchild 7-1');

DECLARE

• #Test 1 CURSOR toccur RETURN Toc%ROWTYPE IS SELECT * FROM Toc START WITH toc_id = 9 CONNECT BY PRIOR prevtoc_id = toc_id ORDER BY LEVEL DESC;

tname VARCHAR2(32);
-- <- # Test 1

• #Test 2 -> CURSOR TocDefCur IS SELECT toc_id, prevtoc_id, ordnnr, name, LEVEL FROM Toc START WITH toc_id = 9 CONNECT BY PRIOR prevtoc_id = toc_id ORDER BY LEVEL DESC;

TYPE NToc_type IS TABLE OF TocDefCur%ROWTYPE;

ntoctab NToc_type;
-- <- # Test 2

BEGIN

-- #Test 1
--      FOR tocer IN toccur LOOP
--         tname := tocer.name;
--      END LOOP;

-- #Test 2 POPULATE ntoctab FROM TocDefCur.
-- FETCH TocDefCur INTO ntoctab; NO !
--      SELECT * INTO ntoctab FROM TocDefCur; NO !

END; Received on Thu Dec 02 1999 - 07:44:02 CST