Skip to Main Content
  • Questions
  • Exporting Clob fields to Excel - getting an empty file at export

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, valerie.

Asked: May 12, 2021 - 5:02 pm UTC

Last updated: May 25, 2021 - 2:26 am UTC

Version: 20.2.0.175

Viewed 100+ times

You Asked

Hello

I'm using SQL Dev 20.2 and since I've upgraded from 17.2, I'm having issues exporting to Excel any data that has a Clob field in it. The data gets extracted and shows on SQL Developer okay but when I export it to Excel it creates an empty 4KB file. Works in 17.2 as tested by a co-worker who has not yet upgraded. Is there a setting needed or a bug fix? I see that V 20.4 is available for download now internally.

Update - I upgraded to version 20.4 and it resolved the issue with the export. All is well. Please close out this question.

and we said...

Publishing in case anyone has same issue.

Rating

  (3 ratings)

Comments

Same here...

J. Laurindo Chiappa, May 14, 2021 - 2:17 pm UTC

Same here : using SQL Developer version 20.4.0.379, exported a table containing a CLOB column to Excel 2003+ (XLSX) format, only the records containing CLOBs smaller than 32 Kb were saved in the .XLSX file and the size of the generated file was 4 Kb (don´t mind the messages and the name of the file in Portuguese) :

C:\>dir C:\Users\Forms\exportar.xlsx
O volume na unidade C não tem nome.
O Número de Série do Volume é 5C20-573C

Pasta de C:\Users\Forms

14/05/2021 10:50 3.897 exportar.xlsx
1 arquivo(s) 3.897 bytes
0 pasta(s) 86.027.997.184 bytes disponíveis

Connor McDonald
May 19, 2021 - 3:54 am UTC

I'll ask the SQL Dev guys and find out if there is a restriction on >32k

Found the bug, I think

J. Laurindo Chiappa, May 19, 2021 - 1:51 pm UTC

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 documents
32,767 characters as Total number of characters that a cell can contain....

Using SQL DEVELOPER 20.4.0.379 Build 379.2205 (connect to a 10.2.0.5 EE database under Windows 2008 64-bits, in my case) , I have this table :

chiappa@DESENV:SQL>@desc TB_TESTE_CLOB_STR

Nome Nulo? Tipo
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 CLOB

With this data :

chiappa@DESENV:SQL>select c1, dbms_lob.getlength(c2) from TB_TESTE_CLOB_STR order by 1;

C1 DBMS_LOB.GETLENGTH(C2)
---------- ----------------------
1 76
2 102
3 0
4 11041
5 983012
6 32000
7 16

7 linhas selecionadas.

Problem is, exporting data to EXCEL 2003+ format (single file) I got an EMPTY cell for the data at line#5 column C2 (not only the first 32767 characters OR some kind of erros message/warning) AND I got no row for line#6 or line#7....
It appears to be some kind of buffer overflow when CLOB is greater than 32767 and from this point onwards, no data is exported....

Regards,

J. Laurindo Chiappa

Connor McDonald
May 24, 2021 - 5:05 am UTC

I spoke to the SQL Dev team - they basically said similar, ie, 32k is going to be a limit, and they'll opt for no data rather than a subset (because it could easily be interpreted as complete).

If people really want to unload LOB data, perhaps the Loader format is best, because it will unload each LOB as its own file.


Just a small doubt...

J. Laurindo Chiappa, May 24, 2021 - 12:49 pm UTC

Okdoc, LOBs greather than 32 kb are not exported , granted... But what about my example, where the small LOB in the next record after the big LOB is not exported, could it be a bug ?

Regards,

Chiappa
Connor McDonald
May 25, 2021 - 2:26 am UTC

Reproduced this with:

SQL> create table t ( pk int, c clob );

Table created.

SQL> insert into t values (1, rpad('x',1000,'x'));

1 row created.

SQL> insert into t values (2, rpad('y',32000,'y'));

1 row created.

SQL> insert into t values (3, rpad('z',32000,'z'));

1 row created.

SQL> update t
  2  set c = c||c||c||c
  3  where pk = 3;

1 row updated.

SQL> insert into t values (4, rpad('x',1000,'x'));

1 row created.

SQL> insert into t values (5, rpad('y',32000,'y'));

1 row created.

SQL> commit;

Commit complete.


I'll pass it on to the team

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here