• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

identify column in oracle

Hi

Is there a way to identify a column value in oracle that has decimal places, I know one way is to find all data types to see a number(25,2) but in a schema that has about 300 odd tables it is very tedious process - is there a way to single out few tables and then find out a way to see the values that have decimal places? sometimes the value itself will not be decimal place and still it would be a row in the table with the data type number(25,2). so I want to get only rows in that table that has decimal value.
0
mahjag
Asked:
mahjag
  • 4
  • 2
  • 2
1 Solution
 
sdstuberCommented:
query dba_tab_cols to narrow down your candidates
then query the tables to find values that aren't integers
0
 
mahjagAuthor Commented:
Thanks sdstuber for your post - is there a function to test column value is not integer?
0
 
sdstuberCommented:
not directly but you can test if the value equals its own truncation, round, ceiling or floor.

if you want to get a little more obscure mod with 1  for integers will equal 0


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
OP_ZaharinCommented:
hi mahjag,
- you can query to ALL_TAB_COLUMNS to find the column datatype that contain decimal points. to narrow down to column that can contain decimal value, you can specify the where clause to DATA_TYPE = number and DATA_SCALE > 0. DATA_SCALE is where we specify the number of digits to the right of the decimal point. the same syntax can be used for dba_tab_cols.

SELECT owner, table_name, column_name, data_type, data_length, data_precision, data_scale
FROM ALL_TAB_COLUMNS 
WHERE 
data_type = 'NUMBER' 
AND data_precision IS NOT NULL
AND data_scale > 0

Open in new window


"so I want to get only rows in that table that has decimal value"
- after identifying which table and column probably have decimal value from the sql above. if you need to query if that column have any decimal value, you can use the following sql:

SELECT * FROM tblname WHERE columnname LIKE '%.%'

Open in new window

0
 
mahjagAuthor Commented:
Hi

i actually need to find from data that they are integers or decimals - sometimes you can store integer value in decimal data type..
0
 
sdstuberCommented:
right   the trunc,round,floor,ceil functions or the mod trick or the string conversion like '%.%'  should all work for that
0
 
OP_ZaharinCommented:
mahjag, referring to your " .. is there a way to single out few tables .."
- i would like to know, what/which method do you used to narrow down to which table/column to look for the decimals?
0
 
sdstuberCommented:
there's really only one way


xxxx_tab_cols   (dba, all, user)

even xxxx_tab_columns  is just built on the corresponding xxxx_tab_cols
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now