Is DECIMAL_DIGITS an Oracle field, and if not, how do I get that info?

Question

I've been using dbVisualizer as a quick-and-dirty database viewer, since I'm unfamiliar with Oracle and don't have sqlplus/gqlplus available. I assumed that its "columns" view was showing me the same metadata for a table that Oracle would. The headers in that view are:

BUFFER_LENGTH      DECIMAL_DIGITS    SQL_DATA_TYPE
CHAR_OCTET_LENGTH  IS_NULLABLE       SQL_DATETIME_SUB
COLUMN_DEF         NULLABLE          TABLE_CAT
COLUMN_NAME        NUM_PREC_RADIX    TABLE_NAME
COLUMN_SIZE        ORDINAL_POSITION  TABLE_SCHEM
DATA_TYPE          REMARKS           TYPE_NAME

My application is pretty straightforward, but it does have a couple of integer fields and a couple of decimal fields, and I want to be able to dump that information easily as I go through the development process.

So I queried the USER_TAB_COLS table via Python's cx_Oracle, and asked for, among other things, DECIMAL_DIGITS. I got "cx_Oracle.DatabaseError: ORA-00904: "DECIMAL_DIGITS": invalid identifier". The other columns I asked for were fine.

So I queried USER_TAB_COLS directly through cx_Oracle, and got these fields:

AVG_COL_LEN           DATA_TYPE_MOD       NULLABLE
CHAR_COL_DECL_LENGTH  DATA_TYPE_OWNER     NUM_BUCKETS
CHAR_LENGTH           DATA_UPGRADED       NUM_DISTINCT
CHAR_USED             DEFAULT_LENGTH      NUM_NULLS
CHARACTER_SET_NAME    DENSITY             QUALIFIED_COL_NAME
COLUMN_ID             GLOBAL_STATS        SAMPLE_SIZE
COLUMN_NAME           HIDDEN_COLUMN       SEGMENT_COLUMN_ID
DATA_DEFAULT          HIGH_VALUE          TABLE_NAME
DATA_LENGTH           HISTOGRAM           USER_STATS
DATA_PRECISION        INTERNAL_COLUMN_ID  V80_FMT_IMAGE
DATA_SCALE            LAST_ANALYZED       VIRTUAL_COLUMN
DATA_TYPE             LOW_VALUE

It just so happens that the other fields I'm interested in (table name, column name, data type, and char length) have the same names in the two lists. But not DECIMAL_DIGITS.

So, if that's what cx_Oracle gives me, that's fine; I would just run with that, since that's what I want to use to dump my schema (for further processing downstream). But I can't figure out how to get the same information from the larger list of fields. DATA_PRECISION is "None" for all my fields, regardless of whether they were declared as INT or NUMBER(*, 4) at table creation. Likewise, DATA_LENGTH is 22 for all my numeric fields.

Where I'm coming from: I have an old and rusty background in web/database development using Postgres and dedicated middleware systems like ColdFusion and Zope, but I'm new to Oracle. Thanks for any and all help you're willing to provide!

Answer 1

Ah ha! I think I've got it -- it's DATA_SCALE. I was confusing DATA_SCALE and DATA_PRECISION, which are the two parameters you can give to a NUMBER field in Oracle. Somehow it always seems to me like the precision should be the part after the decimal point, and the scale should be the total number of digits, but it's actually the reverse.

I'll leave the question open for a bit, because someone may wish to explain it better than I have here; and I'm also still hoping someone will know why different ways of looking at the schema give me such different slates of information.