I'm new to Oracle and database administration in general.
As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE
.
I cannot find any specific way to do that in Java, however I found that ALL_TAB_COLUMNS
could give me similar info. I tried it in SQL Developer to see how different the output was. It turns out the results are a lot more different that I was expecting.
I was hoping someone could walk me through how to interpret the following:
desc SOME_TABLE;
select
COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, NULLABLE
from ALL_TAB_COLUMNS
where TABLE_NAME='SOME_TABLE'
order by column_id;
Gives the output:
Name Null Type
--------------- ------- ----------------------
UIDPK NUMBER(20)
NAME VARCHAR2(255)
2 rows selected
COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE
--------------- -------------- -------------- --------
UIDPK NUMBER 22 N
UIDPK NUMBER 22 N
UIDPK NUMBER 22 Y
NAME VARCHAR2 255 N
NAME VARCHAR2 255 Y
NAME VARCHAR2 255 N
6 rows selected
Why is each column repeated 3 times? Why is the datatype
and length
different on UIDPK
and why is NULLABLE
not the same?
Best Answer
Most probably there are three schemas (users) that contain that table. You need to include
OWNER = 'FOOBAR'
in your query to all_tab_columns (or useuser_tab_columns
). You can also include theOWNER
column in your select list to verify this.For
VARCHAR
(and other character columns) the size is stored inCHAR_LENGTH
as documented in the manual. Note that you also need to checkCHAR_USED
to find out if the definition is in bytes or characters (VARCHAR(10 Bytes)
vs.VARCHAR(10 Char)
).For numeric columns the definition is stored in
DATA_SCALE
andDATA_PRECISION
.This is all described in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2103.htm#I1020277
Are you aware that you can retrieve the full definition of a table using `DBMS_METADATA using a SELECT statement: