Re: Can I have an explicit cusor like this?

"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

Replace the PLUS by UNION
The performance is likely going to be disastrous.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address