Manoj,
Nothing helps your sql skills like traversing the data dictionary,
put on your hip waders get out your TUSC posters, a magnifying glass, a
bottle of scotch and dive in. Here is one way, by no means the best or the
only.
SELECT A.column_name, A.data_type,
DECODE(A.data_type,'NUMBER',
DECODE(A.data_precision,NULL,A.data_length,A.data_precision),
A.data_length) as Length,
A.nullable, min(con_p) || min(con_c) || min(con_r), column_id
FROM
user_tab_columns A, (select B.constraint_name con_name,
DECODE(B.constraint_type,'P','P',null) con_p,
DECODE(B.constraint_type,'C','C',null) con_c,
DECODE(B.constraint_type,'R','R',null) con_r,
B.table_name tname, C.Column_name col_name
FROM user_constraints B, user_cons_columns C
WHERE B.constraint_name = C.constraint_name
AND B.Table_name = 'AG_PRODUCT'
) my_all_cons
WHERE
A.column_name= my_all_cons.col_name (+) AND
A.table_name = my_all_cons.tname (+) AND
A.table_name = 'AG_PRODUCT'
GROUP BY A.column_name, A.data_type,
DECODE(A.data_type,'NUMBER',
DECODE(A.data_precision,NULL,A.data_length,A.data_precision),
A.data_length),
A.nullable,
column_id
ORDER BY COLUMN_ID
If you like recursive SQL, take the table_name out of the inline view and
join my_all_cons.tname = A.table_name in the outer. Great for watching
recursive sql for those with Unabridged Data Dictionaries........
HTH,
Scott Crabtree
-----Original Message-----
Sent: Monday, May 14, 2001 11:31 AM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Scott Crabtree
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).
