I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a
ref cursor would be faster and more efficient. Does anyone know?
When you said temp table, I am assuming that you are creating temp table and returning the data from temp table.
I guess it will be faster to return the data from refcursor instead of loading into a temp table which might take some time.
CREATE OR ALTER PROCEDURE GETDATA(RS OUT SYS_REFCURSOR)
AS
BEGIN
--DO SOMETHING WITH MAIN TABLE
OPEN RS FOR
SELECT * FROM MAINTABLE
-- WHERE ADD YOUR CONDITIONS
END;
Member
7 Points
41 Posts
Oracle ref cursor or temp table?
Sep 09, 2010 11:05 AM|kbujnak|LINK
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?
All-Star
37505 Points
8109 Posts
Re: Oracle ref cursor or temp table?
Sep 09, 2010 12:13 PM|sansan|LINK
When you said temp table, I am assuming that you are creating temp table and returning the data from temp table.
I guess it will be faster to return the data from refcursor instead of loading into a temp table which might take some time.
CREATE OR ALTER PROCEDURE GETDATA(RS OUT SYS_REFCURSOR) AS BEGIN --DO SOMETHING WITH MAIN TABLE OPEN RS FOR SELECT * FROM MAINTABLE -- WHERE ADD YOUR CONDITIONS END;