Understanding Oracle's ALL_TAB_COLUMNS

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?

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 use user_tab_columns). You can also include the OWNER column in your select list to verify this.

For VARCHAR (and other character columns) the size is stored in CHAR_LENGTH as documented in the manual. Note that you also need to check CHAR_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 and DATA_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:

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEE', 'SCOTT')
FROM dual;

StackExchange Database Administrators Q#21266, answer score: 6