CLOB file saved via Large Data Editor contains "garbage"

[Ivan C.]

I'm using the Test Window to call a function that returns a CLOB, which I store in a CLOB bind variable. Once the Test Window runs through, I open the CLOB in Large Data Editor, so I can save it locally. The text in the Text tab (and all others) looks correct, so I proceed by clicking on the "Save as..." button, at the top of the pop-up window, choose a location, type a file name and click Save.

So far everything as expected...
The problems start when I try using the file. The CLOB in question is a trace file generated by DBMS_HPROF.ANALYZE, and it needs to be processed by the "plshprof" command-line utility. This utility was failing with a very strange error:
ORA-44325: no entry points found in trace file...

I couldn't figure out what was wrong until I opened the saved file in HEX-editor (I use Notepad++, with HEX-Editor plugin). If you open the file in a regular text editor like Windows Notepad (or even Notepad++), the text appears to be fine. In HEX, however (in my case), the first two characters were some odd hieroglyphs, with hex values ff and fe. Then, there was a 00 character between every "regular" character in the file. For example, the first line of the "regular" file started with the following 3 characters (the actual line is much longer that 3 characters):

P#V

In HEX they showed as

ff fe 50 00 23 00 56

So, my assumption so far is that Large Data Editor is (somehow) adding these ("garbage") characters when saving the file. The workaround is to highlight the content in the Text tab (Ctrl+A), copy it (Ctrl+C) and manually paste it (Ctrl+V) into a text file, in an external editor.

Please fix it (unless it's not a PL/SQL Developer issue).

I'm on Windows 7 Pro, 64-bit
Oracle 12.2 client, 32-bit
PL/SQL Developer 12.0.5.1828 (32-bit) 01.112462

 

[Marco Kalter]

It seems like a Unicode related issue. The garbage is actually the Unicode Byte Order Mark (UTF BOM). We'll fix it.

 

[Nicolas D.]

Ivan,

in the meantime, if that can help you -- below is a script I use to directly download a CLOB from Oracle thru sqlplus. This may help automate your process in a command line if you need.
Note that this is a linux shell script, but it work perfectly if you install "cygwin" on your windows system and run the script under the Cygwin bash.

sqlplus -s user/pws@DB /cygdrive/c/myfolder/finalfile.txt
rm /tmp/temp.txt

Logic is the script under sqlplus in silent mode, and the CLOB is converted to BLOB and converted to Base64 and output using DBMS_OUTPUT.
The output is trap under a temporary file, and then used the linux/cygwin base64 decode to restore the file.
Note that it can easily be modified to handle also BLOB -- just skip the CLOB to BLOB conversion and feed directly the BLOB.
The reason of CLOB->BLOB->Base64 conversion is to ensure I never break the formatting in the process when I output the file in chunk.

If this can help you

Note: /cygdrive/c in cygwin emulate the C:\ from Windows. Refer to cygwin documentation and the cygpath command to convert windows path cygwin path format.