| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can I have an explicit cusor like this?
Sybrand Bakker wrote:
> "C Chang" <cschang_at_maxinter.net> wrote in message
> news:3D178096.184_at_maxinter.net...
> > Daniel Morgan wrote:
> > >
> > > C Chang wrote:
> > >
> > > > Can I pass a variable for table name in an explicit cursor such as
> > > > Cursor name_cur ( p_tablename IN VARCHAR2,
> > > > p_col3 IN VARCHAR2)
> > > > is
> > > > SELECT col1, col2
> > > > FROM p_tablename
> > > > WHERE col3 = p_col3;
> > > > Because I need to use this cursor for a FOR LOOP in BEGIN section of a
> > > > procedure. Using implicit cursor probably too resource costly.
> > > > Or it can not, is there a way to accomplish similar function?
> > > > C chang
> > >
> > > Not in the form you are trying to do it. Look up Native Dynamic SQL and
> > > the OPEN FOR statement.
> > >
> > > Personally, in situations such as you describe I prefer an IF statement
> > > with a static cursor. But often the need for such dynamic SQL arises due
> > > to lack of relational integrity in the architecture. Identical queries
> > > from multiple tables makes me wonder why it isn't one table. And usually
> > > the answer is that it should be.
> > >
> > > Daniel Morgan
> > Daniel:
> > I totally agree with your suggestion that there should be one table.
> > However, the person who created the table(s) prefers to keep the all
> > data in mutliple tables. Of Course, I will try my best to convince him
> > (he has own reason). Or I am thinking of creating a view that combines
> > all tables such as
> > CREATE a_view
> > select col1, col2
> > from table1
> > PLUS
> > select col1, col2
> > FROM table2
> > ..;
> > I never used such kind of PLUS ( horizontal combination of tables)
> > I am not sure it will works, I will try later. Thanks.
> >
> > C Chang
>
>
>
UNION ALL. He has a need to know where they came from and should not eliminate dups.
And, of course, much faster still.
Daniel Morgan Received on Mon Jun 24 2002 - 18:29:01 CDT
 |
 |