Specify number length and precision in materialized view, starting from a pivot

I have a problem with a materialized view in Oracle: I don't know how to specify the length and precision of the numeric fields. This is an example code of the creation of the materialized view:

CREATE MATERIALIZED VIEW view_name AS
            SELECT COD_NDG, {list of products}
            FROM (
                SELECT 
                    a.id_customer, 
                    a.product_name,
                    TO_NUMBER(a.product_price)
                FROM table a
            )
            PIVOT (
                MAX(product_price)
                FOR product_name IN ({list of products})
            

The script correctly creates the materialize view, but the pivoted fields - the list of products - have NUMBER as datatype, without length and precision. I would it to be NUMBER(3,2), but I don't understand how to do that.

I tried to specify '999.99' in the TO_NUMBER function, but it didn't work. I have no other useful thought on how it should be done.

Thanks in advance to anyone willing to help.

You can define the exact datatype of the resulting segment columns by using the CAST function:

CAST(a.product_price AS number(5,2)) 

And so forth. We usually don't do that, however, because it risks numeric overflow if we wrongly predict what range of values will be encountered. It's generally better to let Oracle determine the datatype and its limits in a CTAS/materialized view situation.

Those limits are merely constraints on what is stored in the segment - they should not be confused with presentational format that a human sees at query time. If you are concerned only with the latter, then use functions to format at query time, rather than trying to force limits via datatype.

On the other hand, if your data model requires a 2-digit decimal (maybe a dollar amount with 1 cent being the finest granularity possible) and also requires the limitation to less than $1000 so that 3 digits to the left of the decimal is of the essence of the data you are storing, then it would be appropriate to type it as such. Usually, however, this is not the case. You wouldn't want a value of 1001.00 to fail, so forcing the constraint of number(5,2) (which limits the portion left of the decimal to 3 digits) probably isn't a good idea. If it is for dollar amounts, number(*,2) is probably more appropriate.