Search code examples
oracle-databaseexcelspreadsheetoraclereports

Oracle reports to excel spreadsheet avoid scientific notation

I have an oracle report that outputs to an spreadsheet. The spreadsheet automatically takes large numbers and converts them to scientific notation.

I've tried to avoid this by

1)Prepending a blank space like below in the query

select ' '||24534534534345353 "Really Long Number" from dual;

2)Prepending an apostrophe(') like below in the query

select ''''||24534534534345353 "Really Long Number" from dual;

The above two methods from http://support.microsoft.com/kb/214233 didn't work. I still get the scientific notation.

For 1, the space is simply ignored by excel

For 2, the quote is taken literally and printed along with the number.[Strangely, When I edit the cell by removing/adding a digit after the report is generated, the apostrophe(') goes away.]

I've also tried LPAD()/RPAD() with blank space. Is there any other workaround for this?

Solution

  • I've found the following workaround with the non-breaking space character:

    select 24534534534345353||chr(160) "Really Long Number" from dual;

    It isn't very neat but this is the closest that I could get.