Search code examples
sqldatabaseoracleoracle11g

SELECT COLUMN NUMBER OF DECIMAL PLACES FROM TABLE DEFINITION

So let's say I have a table that is simply defined by:

create table foo (something NUMBER(12, 6))

Can I fetch the number of decimals that this column has in its definition?

So something like

SELECT decimal_numbers(something) as num_decimals FROM foo

Should return in this case 6.

Solution 
  • select table_name, column_name, data_type, data_precision, data_scale
from user_tables t
where table_name='FOO'

    Particularly, the data_scale should give you the number of decimal places for the NUMBER columns. Anyway, keep in mind this data dictionary table, which will prove helpful. Even more helpful will be all_tab_columns, giving columns of tables for which you have some grants even if they are not in the schema for the user which is logged on. And if you have access to sys.dba_tab_columns (which depends on what the DBA granted to that user), then it would be just great. RTM on the Oracle data dictionary tables - quite a lot to read...