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
LOOP
 APPEND rows from pre_cursor to main_cursor
END LOOP
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
Mathias

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
try:

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, c.id 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, c.id

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:
CURSOR main_cursor(dMax IN DATE, sOrg IN VARCHAR2, nId IN INTEGER) IS
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>
ORDER BY ...

As you can see, main_cursor depends on pre_cursor:
FOR temp_record IN pre_cursor
LOOP
 OPEN main_cursor(temp_record.dMax,temp_record.sOrg,temp_record.nId)
/* I actually want to append rows to main_cursor */
END LOOP
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
Mathias

 
 
 

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
        LOOP
            OPEN main_cursor(pre_record.dMax, pre_record.sOrg, pre_record.nId);
                LOOP
                    i:=i+1;
                        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.

//Mathias

 
 
 

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
varchar(2000))

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?