Re: Can I have an explicit cusor like this?

C Chang wrote:
>
> 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

Hi Daniel:

   Thanks. I created a view to union all 6 tables and a synonym for that ( because i am calling it from other schema). Even with 6 miliion records, the package use that view is returning requested records smoothly. I will keep trying with different situations. right now, the speed of returning from web page call is OK with average 1500 records. Thanks again.

C Chang Received on Tue Jun 25 2002 - 21:16:27 CDT