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

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