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

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

And that way you don't get the duplicates.

Alternatively you could always query all_tab_columns for a list of columns and make a guess.

But I don't see what you are doing as dynamic queries ... they look completely static to me. If you want dynamic queries look at native dynamic SQL and the DBMS_SQL built-in package.

BTW: I have worked on projects where people produce WHERE clauses dynamically and they always suffer from one overriding fault ... those writing the C or XML or JAVA or whatever code never have a clue about indexes and produce some of the worst performing SQL I have ever seen. If you are thinking about modifying your WHERE clauses dynamically my advice is don't ... build a package in the front-end and just pass parameters to it. It is far more efficient and far more tunable anyway.

Daniel Morgan Received on Mon Jul 01 2002 - 18:06:28 CDT