Exporting a CLOB to a text file using sqlDeveloper

Question

I am using sqlDeveloper and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal is to not use Oracle here (I received an Oracle dump - which was loaded into an oracle db, but am using the data in another format so going via CSV as an intermediary).

If there are multiple solutions to this, given that it is a one time procedure for me, I don't mind the more hack-ish type solutions to more involved "do it right" solutions.

Answer 1

if you have access to the file system on your database box you could do something like this:

CREATE OR REPLACE DIRECTORY documents AS 'C:\';
SET SERVEROUTPUT ON
DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
  SELECT col1
  INTO   l_clob
  FROM   tab1
  WHERE  rownum = 1;

  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    l_pos := l_pos + l_amount;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM);
    UTL_FILE.fclose(l_file);
END;
/

Which I copied and pasted from this site.

You may also find this previous question about UTL_FILE useful. It addresses exporting to CSV. I have no idea or experience with how UTL_FILE handles CLOBs, however.

Answer 2

assuming by an Oracle dump you meant a .dmp (either from export or expdp), you're looking at a binary file. You'll need to import the dumpfile into an Oracle database and then export the data to plain text using UTL_FILE or other means.

Answer 3

Here is a short yet general python script that does just this - dumping tables (with CLOB fields, among the rest) to a flat csv file: OraDump