Newbie: Returning Result Sets from Cursors

Newbie: Returning Result Sets from Cursors

Post by Howard Shlo » Fri, 11 Dec 1998 04:00:00



I have a cursor in a stored procedure that will loop through and select
certain rows.  How do I pass this back as a resultset?  Do the variables get
moved somewhere?  Can output variables be RS arrays?
Thanks for any help.
Howie
 
 
 

Newbie: Returning Result Sets from Cursors

Post by Tom Hummel, International Superst » Fri, 11 Dec 1998 04:00:00


I accomplish this by creating a temporary table and then returning
that as my result set. Basically it would be something like this:

create table
declare cursor
allocate cursor
while (whatever)
  if a good record is found do an insert into temp table
close cursor
deallocate cursor
select * from temp table
drop table

Make sure that you drop the table at the end. If it is a local temp
table (i.e., name starts with '#') then I think it is automatically
dropped at some point anyway, but I'm a stickler for cleaning up after
oneself. Since the table will be local temporary, you shouldn't need
to worry about concurrency issues (anyone want to clarify this?) such
as two users running the same SP and messing with the same temp table.
Just make sure that you don't use '##' as the table name prefix.

        HTH,
          -Tom.


>I have a cursor in a stored procedure that will loop through and select
>certain rows.  How do I pass this back as a resultset?  Do the variables get
>moved somewhere?  Can output variables be RS arrays?
>Thanks for any help.
>Howie



 
 
 

Newbie: Returning Result Sets from Cursors

Post by Sapna S. Sharm » Fri, 11 Dec 1998 04:00:00


Hello Howard,
You can save the values after fetching each rows from the curso, in a temp table
and then finally return the recordset from selecting
* from temptable.

1.Create TempTable
2.Create Cursor
3.Loop in the cursor, get the values
4.Insert in Temptable the fetched values
5.Select * from tempTable.

Hope this helps.
SAPNA


> I have a cursor in a stored procedure that will loop through and select
> certain rows.  How do I pass this back as a resultset?  Do the variables get
> moved somewhere?  Can output variables be RS arrays?
> Thanks for any help.
> Howie

 
 
 

1. returning result sets/cursors from a stored procedure

Everything I have seen to date seems to indicate that it is not possible
to return a handle/cursor to a result set from a procedure written in
Java. Is this true? Target environment of interest is DB2 UDB.

Note: using techniques similar to the pre-7.3 ORACLE PL/SQL approach to
faking out returning rows is not appropriate for my application.

  Nigel.Campbell.vcf
< 1K Download

2. Query Question

3. Cursor for result set returned by stored proc?

4. 7348-NY-NEW YORK-ORACLE-DBA Skills-Database Administrator

5. how to set sp_executesql result sets to a cursor

6. buffer cache hit vs cache hit: what's the difference?

7. Return result set and return value/output parameter

8. Result Sets vs copying result sets into memory.

9. Oracle and ODBC: returning cursor results from stored function

10. SQL Cursor only returns 24 results

11. Crazy Cursor Problem - results not returned

12. Returning cursor results from Oracle stored function