Re: Question about origin fieldname and tablename

Jürgen Bauer wrote:

> Hello, all together,
>
> we are planning to port our application which is written in Delphi to
> Oracle. Our program supports currently MSSQL via ADO and INTERBASE natively.
> We took a look at Oracles ADO driver. But the performance is to weak. So we
> looked at some other products which talk directly via OCI with the Oracle
> database.
> Unfortunately, these products offer no information about the origin
> fieldname and tablename.
> When we select a dataset with the following statement:
>
> SELECT a.NAME as SORTNAME, c.*
> FROM ADRESSES AS a, CONTACTS AS c
> WHERE a.ID = c.MASTERID
>
> we need to know the original fieldname "NAME" of the field "SORTNAME" and we
> need to know the corresponding tablenames to the fields.
> The ADO driver gives these properties via "BASETABLENAME" and
> "BASEFIELDNAME". So there must a be solution to obtain these values via a
> OCI call.
>
> Can somebody give us a tip?
>
> Thanks very much for your time.
>
> Greetings,
> Jürgen Bauer
> BauerSoftware, Germany

All of this information is available in the data dictionary views:

SELECT column_name
FROM all_tab_columns
WHERE owner = ...
AND table_name = ...

But why are you writing such code in the first place? Why are you aliasing column names? Why are you using 'AS' at all?

Either way if you can write this:

 > SELECT a.NAME as SORTNAME, c.*
 > FROM ADRESSES AS a, CONTACTS AS c
 > WHERE a.ID = c.MASTERID

Then you already know that SORTNAME's base column is NAME. Which, by the way, is a reserved word in Oracle and should never be used as a column name. Same goes for ID ... it is a reserved word.

SELECT keyword
FROM v$reserved_words;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)