SQL Nav 7.1 : Export data from Clob field

SQL Navigator
1

Hi

I tried to export some records from a table containing a clob field :

clob_1.png
clob_1.png861×558 13.7 KB

Instead of the content of the clob field, I get this :

INSERT INTO prg_tests (NUM_ERREUR,PRG_TEST) VALUES(10002,EMPTY_CLOB());
INSERT INTO prg_tests (NUM_ERREUR,PRG_TEST) VALUES(10003,EMPTY_CLOB());

Is there a way to get the values of the clob fields in an export ?

Regards

Martin

2

Interesting find. It looks like it depends what format you export as. I just tried it in SQLN 7.1.0.3676 and if I export it as Table Inserts like your screen shot shows, I also get the empty_clob() field. If I export it as delimited text, I just get the word “(CLOB)”. Yet if I export as HTML, XML, Spool text, or Excel, I do get the CLOB contents. I would think this should at least be an export option for the table insert or delimited text exports (Include BLOB/CLOB content: Y/N).

3

Hi Martin,

Thanks for your report this issue. we have fixed this issue in v7.2 Beta, so v7.2 will full support export clob field, and we will sent out the beta soon.

For v7.1, we have workaround for you:

  1. right click on data grid and export data

  2. select format: excel 97-2010

  3. change .xlsx to .xls in file option

  4. Click OK to export

Thanks,

Michael

4

Michael, to follow up on the original thread. I have the same issue as the original poster. While attempting to save results as Table INSERTs (not Excel), I get the EMPTY_CLOB for long character fields. Is there a setting that I could have accidentally flipped? I’m thinking maybe, because this was working OK last week. In TOAD, I helped a user with simiar issue fix the appropriate setting, but I can’t find where the setting is in SQL*Nav.

I’m on SQL*Nav 6.3.

Thanks

5

Hi Jimmy,

Thanks for your feedback, we have created a new story (SQLNAV-1800 :Get EMPTY_CLOB() when export data from CLOB field with Table INSERTs) for this problem.

In addition, we also offer some workarounds for you:

  1. you can export data from CLOB field with Spool Text:

2.upgrade SQLNav to version 7.2, you can use "Export LOBs "(in the right-click menu) to export COLB field data:

3.Or in accordance with the way Michael said to do.

6

Hi Michael

Just got the time to test this point in V7.2 beta : still the same problem.

Regards

Martin

7

Hi Martin,

So far, the V7.2 still can’t support export data from CLOB field with Table INSERTs ,we have created a new story (SQLNAV-1800) for this;

Besides, if COLB stored a non-text object such as picture, SQLNav can’t export data from this type unless it’s a text object.

if the COLB stored a text object, you can export data with “HTML”, “Spool Text”, “Excle 97-2003” and “XML”;

if the COLB stored a non-text object, you can use "Export LOBs "(in the right-click menu) to export non-text object data.

I hope this will help you .

Regards,

Alan

8

Hi Alan

In my case the clob fields only contain text data. It used to be a varchar field, but I got size problems, therefore I had to chose the clob type.

I use the export to reinsert the selected records in distant databases, the other formats are not usable therefore.

Regards

Martin

9

Alan, thank you for confirming my issue with exporting CLOBS when using TABLE INSERT.

Just to clarify, when I said it had working previously, that was in fact for a different type, LONG CHARACTERS. Looking forward to the patch for CLOBs in future releases. Thanks for your prompt reply.

10

Hi

Tested solution in “table insert” and “excel” export, works fine. Thanks

SQL Navigator 7.2.png

Regards

Martin