Re: Retreiving Column/Table Names from a Query Using OCI

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Tue, 2 Jul 2002 22:39:43 +0100
Message-ID: <3d221da6$0$8508$cc9e4d1f@news.dial.pipex.com>

"Flavius Vespasianus" <nl_at_nl.com> wrote in message

news:VkhU8.712$2k5.300_at_newsread1.prod.itd.earthlink.net...

> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in
> news:3D20E051.6D0DC841_at_exesolutions.com:
>
> > Flavius Vespasianus wrote:
> >
> >> I have an application that I am trying to switch from "BDE to ODBC to
> >> oracle" to OCI directly.
> >>
> >> The problem I have having is that the application uses dynamic
> >> queries.
> >>
> >> If I have a query like
> >>
> >> SELECT * from A, B WHERE A.X = B.X
> >>
> >> I need to know in the result set which columns come from A and which
> >> come from B. Unfortunately, when I use the examples in the OCI
> >> manual, only the column name gets returns.
> >>
> >> How can I get the table name for a column in a result set?
> >
> > You could solve this problem in a number of ways. The one I would
> > recommend is to write good SQL.
> >
> > Rewrite your queries to:
> >
> > SELECT a.field_name, a.field_name, b.field_name
> > FROM table_a a, table_b b
> > WHERE a.x = b.x
> >
>
> Unfortunately, that is impossible with this application.

Then I'm pretty sure that what you want is impossible with this app as well. Unless anyone knows a way of solving this without rewriting the query.

SQL> create table a(col1 number);

Table created.

SQL> create table b(col1 number,col2 number);

Table created.

SQL> insert into a values 1;
insert into a values 1

                     *

ERROR at line 1:
ORA-00906: missing left parenthesis

SQL> ed
Wrote file afiedt.buf

  1* insert into a values(1)
SQL> / 1 row created.

SQL> insert into b values(1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a,b where a.col1=b.col1;

      COL1 COL1 COL2
---------- ---------- ----------

         1 1 2

SQL> insert into b values(1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a,b where a.col1=b.col1;

      COL1 COL1 COL2
---------- ---------- ----------

         1          1          2
         1          1          2

Received on Tue Jul 02 2002 - 16:39:43 CDT