Re: Cursors

On Jan 7, 1:07 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi All,
>
> I've looked over some internet pages and have not found the solution
> yet.....
>
> I have a cursor which is the same in terms of columns for 5 tables,
> except the table name is different.  Can I create some type of REF
> cursor or something where I can change the table name so I can use the
> same select?
>
> Not sure if this can be a cursor parameter......
>
> Not my design, so I cannot combine the tables......
>
> Thanks!!

A REF CURSOR is used for passing the output of a select statement to another process:

declare

   type mycur is ref cursor;
   myrefcursor mycur;
begin

   open myrefcursor for select * from user_objects; end;
/

You COULD build a query string and substitute the table_name:

declare

   type mycur is ref cursor;
   myrefcursor mycur;
   cursor get_tabname is
   select table_name from user_tables;
begin

   for tabname in get_tabname loop

           open myrefcursor for 'select * from '|| tabname.table_name||' where 0=1';

   end loop;
end;
/

And possibly that's what you're wanting.

David Fitzjarrell Received on Mon Jan 07 2008 - 16:17:30 CST