Re: Cursors

From: <amerar_at_iwc.net>
Date: Mon, 7 Jan 2008 14:21:11 -0800 (PST)
Message-ID: <a724ae78-9efc-4da9-a339-424c560676bd@j78g2000hsd.googlegroups.com>


On Jan 7, 4:17 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

Got it done. Thanks! Received on Mon Jan 07 2008 - 16:21:11 CST

Original text of this message