OraFAQ Forum: SQL & PL/SQL » how to get column names from the table

Home » SQL & PL/SQL » SQL & PL/SQL » how to get column names from the table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get column names from the table [message #632930]

Sat, 07 February 2015 13:10

orcle_us
Messages: 21
Registered: February 2015

Junior Member

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named st_records that contains student_id,name, english, maths then I would want to retrieve say column english (without data)from the query

Can you help me please?

Many thanks

Report message to a moderator

Re: how to get column names from the table [message #632932 is a reply to message #632930]

Sat, 07 February 2015 13:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/m/632923/#msg_632923

SQL> DESC ST_RECORDS

SQL> DESC USER_TAB_COLUMNS

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: how to get column names from the table [message #632934 is a reply to message #632932]

Sat, 07 February 2015 13:31

orcle_us
Messages: 21
Registered: February 2015

Junior Member

I want to use that specific column in my PL/SQL block to print the column name as output.

Report message to a moderator

Re: how to get column names from the table [message #632937 is a reply to message #632934]

Sat, 07 February 2015 14:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Nobody here is a mind reader.
we don't know what you have unless & until you SHOW us.
we don't know what you want until you SHOW us.

We can't write code without know table names, column names & complete requirements.

NEVER do in PL/SQL that which can be done in plain SQL.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>I want to use that specific column in my PL/SQL block to print the column name as output.
nobody here prevents you from doing anything you desire & are capable of doing.

Report message to a moderator

Re: how to get column names from the table [message #632938 is a reply to message #632937]

Sat, 07 February 2015 14:14

orcle_us
Messages: 21
Registered: February 2015

Junior Member

table name is st_records
column names in my table are student_id, name, english, maths
I am trying to retrieve th names of "column number 3 and 4" which now are english and maths, without data.

SQL> desc st_records;
Name Null? Type
----------------------------------------- -------- ---------------------

SN NUMBER(3)
ST_ID CHAR(40)
NAME VARCHAR2(20)
ENGLISH NUMBER(3)
MATHS NUMBER(3)

Report message to a moderator

Re: how to get column names from the table [message #632939 is a reply to message #632938]

Sat, 07 February 2015 14:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"Column 3q and 4" is something meaningless in relational database.
Columns are in no order.
You must NOT rely on columns place.

[Updated on: Sat, 07 February 2015 15:29]

Report message to a moderator

Re: how to get column names from the table [message #632940 is a reply to message #632938]

Sat, 07 February 2015 14:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select column_name from user_tab_columns where table_name = 'EMP' and column_id in (3,4);

COLUMN_NAME
------------------------------
JOB
MGR

Report message to a moderator

Re: how to get column names from the table [message #632941 is a reply to message #632940]

Sat, 07 February 2015 16:14

orcle_us
Messages: 21
Registered: February 2015

Junior Member

you are a great help for a beginner like me. Thank you both .

when i type in

SQL> desc st_records;
Name Null? Type
----------------------------------------- -------- ----------------------------

SN NUMBER(3)
ST_ID CHAR(40)
NAME VARCHAR2(20)
ENGLISH NUMBER(3)
MATHS NUMBER(3)

SQL> select column_name from user_tab_columns where table_name = 'st_records' an
d column_id in (3);

no rows selected

SQL> select english from st_records;

ENGLISH
----------
78
67
75
92
56

SQL> select column_name from user_tab_columns where table_name = 'st_records' an
d column_id in (3,4);

no rows selected

[Updated on: Sat, 07 February 2015 16:23]

Report message to a moderator