Re: Can I have an explicit cusor like this?

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 24 Jun 2002 20:44:58 GMT
Message-ID: <3D1784B1.324AA607@exesolutions.com>

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

Provided common sense and logic don't work a view sounds like an excellent approach but you may well, in your above SQL have answered the question as to how to solve the problem. Why not a cursor like this one?

CURSOR my_cur IS

   SELECT *
   FROM (

      SELECT col1, col2, 'TABLE_1' SOURCE
      FROM table1
      UNION ALL
      SELECT col1, col2, 'TABLE_2' SOURCE
      FROM table2)

   WHERE source = <some passed in parameter>;.

The passed in parameter can be either 'TABLE_1' or 'TABLE_2'.

Daniel Morgan Received on Mon Jun 24 2002 - 15:44:58 CDT