Appending rows to a cursor

Appending rows to a cursor

Post by Mathias Nilsso » Tue, 01 Sep 1998 04:00:00

Is it possible to append rows to a cursor in PL/SQL
(Oracle v7.3.4)? To specify a bit more. I need to loop
through a cursor, passing parameters from that cursor to
a SELECT statement (or cursor), and appending all resulting
rows to a new cursor. When the loop is finished, I need to
return the cursor (with the appended rows) from the function.
In pseudo code it looks something like this:

CURSOR pre_cursor IS SELECT ...
CURSOR main_cursor(parameters) IS SELECT ...

OPEN pre_cursor
 APPEND rows from pre_cursor to main_cursor
RETURN main_cursor

I have posted a similar question once before, but never
gotten a reply. Hopefully I'll have better luck this time.

Thanks in advance

PS Don't forget to remove NOSPAM when replying via email.


Appending rows to a cursor

Post by Mathias Nilsso » Tue, 01 Sep 1998 04:00:00

Since my original post was somewhat confusing, I'll give it another

I have a function that is supposed to return a cursor based on two
different cursors (or SELECT statements if that would be a better
idea). The function has 7 in parameters.

CURSOR pre_cursor IS
SELECT max(a.mydate) dMax, b.organization sOrg, nId
FROM <tables>
WHERE <column 1>=<parameter 1>
AND   <column 2>=<parameter 2>
AND   <column 3>=<parameter 3>
AND   <column 4>=<parameter 4>
AND   <column 5>=<parameter 4>
AND   <column 6>=<column 7>
GROUP BY b.organization,

This cursor gives me multiple rows consisting of 3 columns. I later
use this cursor to send parameters to the second cursor (main_cursor).

The second cursor looks like this:
SELECT <10 different columns>
FROM <tables>
WHERE <column 1>=dMax
AND   <column 2>=sOrg
AND   <column 3>=nId
AND   <column 4>=<parameter 3>
AND   <column 5>=<column 6>

As you can see, main_cursor depends on pre_cursor:
FOR temp_record IN pre_cursor
 OPEN main_cursor(temp_record.dMax,temp_record.sOrg,temp_record.nId)
/* I actually want to append rows to main_cursor */
RETURN main_cursor

This is what I want the function to do, but all I have gotten the
function to do is return rows that depend on the last row in pre_cursor.
It looks like when I am opening main_cursor in the loop, the old rows
get overwritten.

If anyone has a solution for this I would certainly appreciate it.
Thanks again


Appending rows to a cursor

Post by Mathias Nilsso » Wed, 02 Sep 1998 04:00:00

I have found a solution to my problem. The trick was to use PL/SQL
Tables and User-Defined Records:

    FOR pre_record IN pre_cursor
            OPEN main_cursor(pre_record.dMax, pre_record.sOrg, pre_record.nId);
                        FETCH main_cursor INTO my_table(i);
                        EXIT WHEN main_cursor%NOTFOUND;
                END LOOP;
                CLOSE main_cursor;
        END LOOP;
    RETURN my_table;

Now I only have to figure out how to call the function from java...
Anyway, thanks to all people who emailed me suggestions how to solve
my problems.



1. Insert or update from n number of rows into a single row without a cursor

I am looking to do the following without using a cursor.

I have two tables:
                                   Source (i int, desc varchar(10))
                                   Destination (i int primary key, desc

I would like to append all desc of each i value into a second table
A value in Destination may have (0 - n) number of rows in Source.

For example:

    Source (1, 'A')
    Source (1, 'B')
    Source (1, 'Q')
    Source (1, 'Z')
    Source (2, 'A')
    Source (3, 'C')
    Source (4, 'B')
    Source (4, 'Q')

Would result in:

   Destination (1,'ABQZ')
   Destination (2,'AC')
   Destination (3,'C')
   Destination (4,'BQ')

Any ideas?

2. Errors converting SQL 6.5-Logins to SQL 7.0-Logins

3. row-by-row operation without cursor

4. Ins and outs of Pdox

5. How can I append record into cursor?

6. ADO - SQL Server

7. APPEND FROM cursor

8. Maximum size of returned data?

9. Appending from Cursor FPW2.6a

10. Appending Rows to Columns

11. Deleting/appending rows in SQL 6.5

12. Appending Unique Rows

13. Append Rows?