SQL Developer 3.1 build 07.42
Windows 7 OS
32 bit install
querying an Oracle 11gR1 database
I'm attempting to export query results in xlsx format. One of the fields of output is sourced from a CLOB datatype, and some records have greater than 4000 characters. It appears to me that the field is truncated during export to 4000 characters, and an ellipsis is appended -- resulting in a character count of 4,003 characters when the file is opened in Excel. When I view the query result set on screen, the full CLOB data appears. I'm checking a test case where there are about 4,060 characters.
1. Is it true that this truncation is happening?
2. Is there a way to change preferences/settings to allow an amount greater than 4000? Say ... 6000 ... if I wanted to make a change to my install.
I cannot seem to use a workaround of an alternate flat-file format. There are double-quotes, line breaks, and carriage returns in the CLOB fields, and when I attempt to import flat file into excel via the import wizard - these 3 character types together cause excel to think that there is more than 1 cell of data.
I tried searching this forum for similar posts, and the internet, but I couldn't find any posts of others mentioning this scenario or solutions.
Thanks, Jeff. I'll be waiting with bated breath!!! Keep those enhancements coming.
Glad I wasn't going crazy. Strangely enough we had some archaic "LONG" datatypes in our system which have finally been converted by our vendor to CLOB. In testing, the LONG has other SQL limitations - but it could pull every character stored; whereas the CLOB was long overdue - but then I was seeing the truncation.
Well Yes, there is a limit of an xlsx cell of 4000 characters.
If possible, you can have your data written in excel file in 2 cells. That will resolve the issue, else you can write that CLOB to a physical file and store the path in excel cell if you want any further processing of that data.