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

From: Jorge Meirim <transparente_at_mail.telepac.pt>
Date: Fri, 23 Apr 1999 23:25:57 +0100
Message-ID: <7fqvmb$2nj@brown.telepac.pt>

create table tabela (field1 number, field2 char, field3 long);

select table_name,column_name,column_id from user_tab_columns where table_name='TABELA';

TABLE_NAME                     COLUMN_NAME                    COLUMN_ID
------------------------------ ------------------------------ ---------
TABELA                         FIELD1                                 1
TABELA                         FIELD2                                 2
TABELA                         FIELD3                                 3
----------------------------------------------------------------------------
----------------------------

create or replace function myfunction ( tablename varchar2,column_number number) return varchar2 is

cursor c1 is
select column_name from user_tab_columns where table_name=tablename and column_id=column_number;

cur_val c1%rowtype;
return_value varchar2(255);

begin

for cur_val in c1 loop
return_value:=cur_val.column_name;
end loop;
return return_value;
END myfunction;
/

show errors

SQL> select myfunction('TABELA',2) from dual;

MYFUNCTION('TABELA',2)

---

FIELD2 SQL> c/2/1/
  1* select myfunction('TABELA',1) from dual SQL> r
  1* select myfunction('TABELA',1) from dual

MYFUNCTION('TABELA',1)

---

FIELD1 SQL> C/1/3/
  1* select myfunction('TABELA',3) from dual SQL> r
  1* select myfunction('TABELA',3) from dual

MYFUNCTION('TABELA',3)

---

FIELD3

_______________-----------------------_______________

Hope this help
(try to find bugs -EX: UPPER() function not stated)

Jorge Meirim

Tim Romano escreveu na mensagem <37207DDD.D46DD308_at_ot.com>...
>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 - 17:25:57 CDT