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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
