oracle-databaseutl-file

Oracle UTL_FILE: how to preserve zero before decimal point

Oracle 19c I have a table with cost_value NUMBER column. When I write the value (0.429) out into a file, it appears as .429

How do I preserve zero before decimal point?

Eugene

Solution

  • One option is to export a string instead of a number. How? By applying the to_char function with appropriate format mask.

    For example:

    SQL> create table test (col number);

Table created.

SQL> insert into test values (0.459);

1 row created.

SQL> insert into test values (13.2);

1 row created.

SQL> insert into test values (1520.325);

1 row created.

    Then you'd

    SQL> select col,
  2         to_char(col, '999G990D000') col_string     --> something like this
  3  from test;

       COL COL_STRING
---------- ------------
      .459        0.459
      13.2       13.200
  1520.325    1,520.325

SQL>