Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Problem in fetching Table Info

Problem in fetching Table Info

From: Soman Manoj <manojdsoman_at_yahoo.com>
Date: Mon, 14 May 2001 07:26:32 -0700
Message-ID: <F001.0030292A.20010514073056@fatcity.com>

Hi Gurus,

I am facing some problem while fetching data from oracle.
I have to fetch all info like
column_name,data_type,data_length,Nullable and primary key for particular table.
For that i had prepared 2 quiries like:
select A.column_name,A.data_type,
2 decode(A.data_type,'NUMBER',
  3
decode(A.data_precision,NULL,A.data_length,A.data_precision),   4 A.data_length) as Length,
  5 A.nullable from
  6* user_tab_columns A where table_name = 'TEST'

Which gives me info as :

COLUMN_NAME                    DATA_TYPE     LENGTH N

------------------------------ --------- ---------- -
FLD1 NUMBER 5 N FLD2 NUMBER 3 N FLD3 VARCHAR2 10 N FLD4 VARCHAR2 30 Y FLD5 VARCHAR2 100 Y FLD6 VARCHAR2 50 Y FLD7 NUMBER 22 Y

Which is fine,but along with this i want info like which fields are pkey for which i have one more query:

select C.column_name,B.constraint_type from user_constraints B,user_cons_columns C
where
B.constraint_name=C.constraint_name and
B.table_name='TEST'

which gives me info as to which are pkeys in table, But when i join these 2 quiries i get only 3 fields which are in primary key like FLD1,FLD2,FLD3

COLUMN_NAME                    DATA_TYPE     LENGTH N
C
------------------------------ --------- ---------- -
-
FLD1                           NUMBER             5 N
P
FLD2                           NUMBER             3 N
P
FLD3                           VARCHAR2          10 N
P

whereas i want all other fields also along with that. Output should be as :

COLUMN_NAME                    DATA_TYPE     LENGTH N

------------------------------ --------- ---------- -
FLD1 NUMBER 5 N P FLD2 NUMBER 3 N P FLD3 VARCHAR2 10 N P FLD4 VARCHAR2 30 Y FLD5 VARCHAR2 100 Y FLD6 VARCHAR2 50 Y FLD7 NUMBER 22 Y

I tried following query for this:
  1 select A.column_name,A.data_type,
  2 decode(A.data_type,'NUMBER',
  3
decode(A.data_precision,NULL,A.data_length,A.data_precision),   4 A.data_length) as Length,
  5 A.nullable,B.constraint_type from

  6   user_tab_columns A,
  7   user_constraints B,
  8   user_cons_columns C

  9 where
 10   B.constraint_name=C.constraint_name and
 11   A.column_name=C.column_name(+) and
 12   A.table_name=B.table_name and
 13*  A.table_name = 'GEMS

Could any one Help me in this??

TIA
Manoj.



Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Soman Manoj
  INET: manojdsoman_at_yahoo.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon May 14 2001 - 09:26:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US