Re: Returning data from temp_tables

On Fri, 03 Aug 2007 12:10:21 -0700, BGT <bgt0990_at_optonline.net> wrote:

>I'm trying to retrieve the results from a temp table where I stored
>results gathered from a cursor but am having trouble. Below are the
>highlights of the code I'm trying to run. How can I get my results
>back from the temp-table and into a cursor to pass back to the
>caller. THERE IS LOTS OF WORKING CODE MISSING
>
>
>
>
>
> PROCEDURE list_mailable(maillist OUT return_cur) IS
> TYPE rec_table is TABLE of result_master%ROWTYPE;
>
> result_temp rec_table;
>
> c3rec result_master%ROWTYPE;
>
> c1rec C2rec.labno%type;
>
>-- This Cursor works fine
>
> OPEN list_cur FOR SQLString
> USING c1rec;
> LOOP
> FETCH list_cur INTO c3rec;
> EXIT WHEN list_cur%NOTFOUND;
> result_temp(vMailable) := c3rec;
> END LOOP;
>
>But everything I have tried to retrieve the rows back into another
>cursor has failed.
>
>Failure #1
>
> open list_cur FOR select * from result_temp;
> loop
> FETCH LIST_CUR into maillist;
> EXIT WHEN list_cur%NOTFOUND;
> END LOOP;
>
>Failure #2
>
> FOR x in 0 .. vMailable LOOP
> select *
> bulk collect into maillist
> from result_temp(X)
> END LOOP;
>
>Failure #3
> FOR x in 0 .. vMailable LOOP
> FETCH result_temp(X) into maillist
> END LOOP;
>
>Can anyone point me in the right direction?
>
>
>
>Barry

I would recommend dumping the temp table. You don't need it, you don't need it at all.
It is as simple as

create or replace procedure foo(bar OUT sys_refcursor) is begin
open bar for 'select * from emp';
end;

And that's all there is to it.

Using your approach, you would need a second ref cursor to CAST the collection to a table.

I would recommend reading the documentation and/or the ref cursor examples on http://asktom.oracle.com (a plethora of them, including samples using various Mickeysoft kludges like Ole, Oledb, ODP or whatever the latest is called) and stop using temp tables. You don't need them, and they make your (cr)app unscalable.

-- 
Sybrand Bakker
Senior Oracle DBA