Re: Get column name from its ordinal position in table?

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Fri, 23 Apr 1999 14:26:40 GMT
Message-ID: <372083FD.52BCA85E@sympatico.ca>

Look at this :

SQL> desc dba_tab_columns

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                     NOT NULL VARCHAR2(30)
 DATA_TYPE                                VARCHAR2(9)
 DATA_LENGTH                     NOT NULL NUMBER
 DATA_PRECISION                           NUMBER
 DATA_SCALE                               NUMBER
 NULLABLE                                 VARCHAR2(1)
 COLUMN_ID                       NOT NULL NUMBER
 DEFAULT_LENGTH                           NUMBER
 DATA_DEFAULT                             LONG
 NUM_DISTINCT                             NUMBER
 LOW_VALUE                                RAW(32)
 HIGH_VALUE                               RAW(32)
 DENSITY                                  NUMBER
 NUM_NULLS                                NUMBER
 NUM_BUCKETS                              NUMBER
 LAST_ANALYZED                            DATE
 SAMPLE_SIZE                              NUMBER

SQL> select column_name from dba_tab_columns   2 where table_name = 'TRK_RESOURCES'   3 order by column_id;

COLUMN_NAME

---

ID
FIRST_NAME
LAST_NAME
WORK_PHONE
TITLE
EMAIL
HOME_PHONE
EXTENSION
LOCATION
WEB_USERNAME
WEB_PASSWORD
CELL_PHONE
FAX_PHONE
SUPL_ACCRONYM 14 rows selected.

SQL> desc trk_resources;

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER(8)
 FIRST_NAME                      NOT NULL VARCHAR2(40)
 LAST_NAME                       NOT NULL VARCHAR2(40)
 WORK_PHONE                      NOT NULL NUMBER(14)
 TITLE                                    VARCHAR2(80)
 EMAIL                                    VARCHAR2(80)
 HOME_PHONE                               NUMBER(14)
 EXTENSION                                NUMBER(6)
 LOCATION                                 VARCHAR2(40)
 WEB_USERNAME                             VARCHAR2(15)
 WEB_PASSWORD                             VARCHAR2(20)
 CELL_PHONE                               NUMBER(14)
 FAX_PHONE                                NUMBER(14)
 SUPL_ACCRONYM                   NOT NULL VARCHAR2(12)

SQL> You can't begaranteed that COLUMN_ID will have sequential values (1,2,3,4,etc.). You will have to loop and count the columns to find the right one.

Regards,

Marc Mazerolle
InforMaze Technologies

Tim Romano wrote:

> Is it possible to write a function that can figure out the name of
> a column if the column's ordinal position is known? If so, I would
> be grateful if someone could point me toward the right system
> table/view(s).
>
> ColumnName = MyFunction(TableName, ColumnPosition)
>
> TIA,
> Tim
Received on Fri Apr 23 1999 - 09:26:40 CDT