Re: Can I have an explicit cusor like this?

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 Received on Mon Jun 24 2002 - 15:27:02 CDT