For NUMBER types, scale is the number of digits after the decimal point. Scale - The function of the scale setting depends on the column type. For example, if a column of type NUMBER has precision of 4, the column value can be 0 through 9999. Only NUMBER, FLOAT, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND have this setting. To get a reasonable approximation, and that is easy enough to craft into something dynamic, egĢ l_template varchar2(1000) := 'select digits, decimals from l_sql varchar2(1000) ħ for i in ( select owner, table_name, column_nameġ3 l_sql := execute immediate l_sql into l_dig, l_dec ġ5 dbms_output.put_line(i.owner||'.'||i.table_name||'.'||i.column_name||'='||l_dig||','||l_dec) Įasily adjusted for either subsets of data etc.Precision - The precision is the number of digits before the decimal point. There is nothing we can see without looking at the data. Without data sampling how do you expect to know anything about a column defined as NUMBER other than it could contain *any* kind of NUMBER ? Other than the limits of the NUMBER datatype I cannot possibly do the mapping manually because I have 600 tables and each table has 50-300 columns. I really doĮxample: tableA column ID has type NUMBER, column amount type NUMBER and column quantity type NUMBER - no precision or scale specified.īut in fact ID should be bigint, amount should be decimal(18,6) and quantity should be int. My last resort is to do a random sampling of data in a table and store conversion schema on a side but I hope there is a better way. My hope Oracle keeps this somewhere in metadata tables but I do not see that anywhere. My question is it possible to detect somehow the actual precision and scale of values stored in a column with NUMBER type. I think it is an issue with all jdbc/java based tools. Now, this is a big problem because when I try to load any of this data to Big Data tools (sqoop, hive, spark etc.) all these tools treat these columns as strings and this is a super painful issue to deal with. But in fact, a column can store pure integer values or decimal values - there is no way to tell that by looking at data type alone. One of our Oracle data sources has hundreds of tables with all numeric columns defined using NUMBER data type without precision and scale.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |