Understanding Oracle's ALL_TAB_COLUMNS

Question

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?

Answer 1

Most probably there are three schemas (users) that have contain 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;

Answer 2

ALL_TAB_COLUMNS is showing you all of the tables you have access to, not just the ones you own. I would guess that there are three schemas on the server with your application database present.

USER_TAB_COLUMNS would show you just the tables you own (i.e. just the ones in the schema belonging to the user you are logged on as).

If you connect as a DBA login you can see another set of data dictionary views called DBA_* which have all of the objects on the instance, regardless of the ownership.

Answer 3

This is not an answer to your question but a comment to your statement that you did not find a possibility to retrieve the information about tablecolumns in java directly. java supplies this information in a database independent way.

Javadoc: java.sql, Interface DatabaseMetaData, getColumns:

ResultSet getColumns(String catalog,
                   String schemaPattern,
                   String tableNamePattern,
                   String columnNamePattern)
                     throws SQLExceptionRetrieves a description of table columns available in the specified catalog. 
Only column descriptions matching the catalog, schema, table and column name criteria are returned. They are ordered by TABLE_CAT,TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION. 

Each column description has the following columns: 

1.TABLE_CAT String => table catalog (may be null) 
2.TABLE_SCHEM String => table schema (may be null) 
3.TABLE_NAME String => table name 
4.COLUMN_NAME String => column name 
5.DATA_TYPE int => SQL type from java.sql.Types 
6.TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified 
7.COLUMN_SIZE int => column size. 
8.BUFFER_LENGTH is not used. 
9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable. 
10.NUM_PREC_RADIX int => Radix (typically either 10 or 2) 
11.NULLABLE int => is NULL allowed. 
•columnNoNulls - might not allow NULL values 
•columnNullable - definitely allows NULL values 
•columnNullableUnknown - nullability unknown 
12.REMARKS String => comment describing column (may be null) 
13.COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null) 
14.SQL_DATA_TYPE int => unused 
15.SQL_DATETIME_SUB int => unused 
16.CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column 
17.ORDINAL_POSITION int => index of column in table (starting at 1) 
18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column. 
•YES --- if the column can include NULLs 
•NO --- if the column cannot include NULLs 
•empty string --- if the nullability for the column is unknown 
19.SCOPE_CATALOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF) 
20.SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF) 
21.SCOPE_TABLE String => table name that this the scope of a reference attribute (null if the DATA_TYPE isn't REF) 
22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF) 
23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented 
•YES --- if the column is auto incremented 
•NO --- if the column is not auto incremented 
•empty string --- if it cannot be determined whether the column is auto incremented 
24.IS_GENERATEDCOLUMN String => Indicates whether this is a generated column 
•YES --- if this a generated column 
•NO --- if this not a generated column 
•empty string --- if it cannot be determined whether this is a generated column 
The COLUMN_SIZE column specifies the column size for the given column. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.

Parameters:
catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
tableNamePattern - a table name pattern; must match the table name as it is stored in the database
columnNamePattern - a column name pattern; must match the column name as it is stored in the database
Returns:
ResultSet - each row is a column description
Throws:
SQLException - if a database access error occurs
See Also:
getSearchStringEscape()