Number format in Oracle SQL

Question

I've given a task of exporting data from an Oracle view to a fixed length text file, however I've been given specification of how data should be exported to a text file. I.e.

quantity            NUM         (10)  
price               NUM         (8,2) 
participant_id      CHAR        (3)   
brokerage           NUM         (10,2)  
cds_fees            NUM         (8,2) 

My confusion arises in Numeric types where when it says (8,2). If I'm to use same as text, does it effectively means

10 characters (as to_char(<field name>, '9999999.99')) 

or

8 characters (as to_char(<field name>, '99999.99')) 

when exporting to fixed length text field in the text file?

I was looking at this question which gave an insight, but not entirely.

Appreciate if someone could enlighten me with some examples.

Thanks a lot.

Answer 1

According to the Oracle docs on types

Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):

If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

So in your case, a NUMBER(8,2), has got:

• 8 digits in total
• 2 of which are after the decimal point

This gives you a range of -999999.99 to 999999.99

Answer 2

I assume that you mean NUMBER data type by NUM.

When it says NUMBER(8,2), it means that there will be 8 digits, and that the number should be rounded to the nearest hundredth. Which means that there will be 6 digits before, and 2 digits after the decimal point.

Refer to oracle doc:

You use the NUMBER datatype to store fixed-point or floating-point numbers. Its magnitude range is 1E-130 .. 10E125. If the value of an expression falls outside this range, you get a numeric overflow or underflow error. You can specify precision, which is the total number of digits, and scale, which is the number of digits to the right of the decimal point. The syntax follows:

NUMBER[(precision,scale)]

To declare fixed-point numbers, for which you must specify scale, use the following form:

NUMBER(precision,scale)

To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can "float" to any position, use the following form:

NUMBER

To declare integers, which have no decimal point, use this form:

NUMBER(precision) -- same as NUMBER(precision,0)

You cannot use constants or variables to specify precision and scale; you must use integer literals. The maximum precision of a NUMBER value is 38 decimal digits. If you do not specify precision, it defaults to 38 or the maximum supported by your system, whichever is less.

Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of 0 rounds to the nearest whole number. If you do not specify scale, it defaults to 0.