Oracle cursor removes leading zero

Question

I have a cursor which selects date from column with NUMBER type containg floating point numbers. Numbers like 4,3433 are returned properly while numbers smaller then 1 have removed leading zero.

For example number 0,4513 is returned as ,4513.

When I execute select used in the cursor on the database, numbers are formatted properly, with leading zeros.

This is how I loop over the recors returned by the cursor:

FOR c_data IN cursor_name(p_date) LOOP

...

END LOOP;

Any ideas why it works that way?

Thank you in advance.

Answer 1

It's the default number formatting that Oracle provides. If you want to specify something custom, you shall use TO_CHAR function (either in SQL query or PL/SQL code inside the loop).

Here is how it works:

SQL>
SQL> WITH aa AS (
  2  select 1.3232 NUM from dual UNION ALL
  3  select 1.3232 NUM from dual UNION ALL
  4  select 332.323 NUM from dual UNION ALL
  5  select 0.3232 NUM from dual
  6  )
  7  select  NUM, to_char(NUM, 'FM999990D9999999') FORMATTED from aa
  8  /

       NUM FORMATTED
---------- ---------------
    1.3232 1.3232
    1.3232 1.3232
   332.323 332.323
     .3232 0.3232

SQL>

In this example, 'FM' - suppresses extra blanks, '0' indicates number digit including leading/trailing zeros, and '9' indicates digit suppressing leading/trailing zeros.

You can find many examples here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570