I’m writing an integration that builds a flat file in a local (to Sun server) file system. The file is comprised of a tab delimited “header row” followed by collection of tab delimited data records. Each “header” corresponds to a column of the data.

I’ve managed to build a DynamicSQL select string that returns the column data in tab delimited records, but now I need to be able to pull the column “aliases”.

(These used to be included in the comma delimited select string I extracted from the database in the form…

MAILING_LINE1 “MAILING_LINE1”, MAILING_LINE2 “MAILING_LINE2”, MAILING_LINE3 “MAILING_LINE3”, MAILING_LINE4 “MAILING_LINE4”, MAILING_LINE5 “MAILING_LINE5”, MAILING_LINE6 “MAILING_LINE6”

which has the column aliases imbedded.)

Now I’ve been asked to pull the aliases from the database itself. I’ve heard that with SQLPlus a SELECT will return the column aliases as the first row of data.

Regardless, what I need is a way through my v6.1 JDBC adapter to retrieve the column aliases.

Thanks!

Hi,

Column aliases are assigned by your application in the SELECT statement, they are not held in the database. I think you can do what you want directly in your application - but perhaps I’ve misunderstood your question…

You assign an alias in your select statement, by adding an alias designation after the column being selected.

e.g. SELECT realColumnNameA “ALIAS A”, realColumnNameB, “ALIAS B”,… etc.

The aliases are defined by YOU as part of the SELECT statement and can be whatever you want (e.g. SELECT orderNumber “CUSTOMER NAME”,… makes “CUSTOMER NAME” the alias for the “orderNumber” column - this is a valid (if ridiculous) SELECT statement). In your example, you seem to be replacing the column names with the same aliases, which is unnecessary - an alias is used to replace the default column name in the results metadata, otherwise the default column name will appear anyway.

Given that you are building the SQL statement - including the aliases - in your application, there is no reason to need to get them from the database - you can just use what you already know in your application code. Have I missed something???

By the way, in SQLPlus the aliases are displayed first (as column headers) when results are returned, but they are NOT part of the results data. More technically, the column aliases form part of the metadata returned with a database result set - the SQLPlus application chooses to display them before displaying the row data.

Hope this helps.

Cheers,

Steve Ovens

Steve,

Thanks for your response. First off, I’m not a DBA and have only had what I’ve been told is how this should work as a basis for my questions. This said…

You observe correctly that the select string I provided had the column aliases imbedded. What I must not have been clear about is that my flow retrieves the select string pre-built FROM the database. (It is put there by a Cold Fushion application.) There is a request from the developers of the front end that builds the select string to only have to provide the column names and to no longer include the aliases. The designer of this front end maintains that the aliases are in fact known to the database and that SQLPlus would have returned them as the first row of data.

You say it doesn’t work that way.

I’m confused.