Exporting a Clob to a Text File Using Oracle SQL Developer

Exporting a CLOB to a text file using Oracle SQL Developer

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.

Export and insert CLOB data containing SQL statements with SQL Developer

I'd use Data Pump if it's available.

If not, I'd use SQL*Loader.

What you can do, is use SQL Developer to unload your table to a SQL*Loader setup, each CLOB will be written to a file, and they can be loaded then w/o issues like what you're seeing.

I wrote this here for how to do this with BLOBS, but would be the same process.

The output will be all the files you need to move your table over to the new system, the control file, the data stream, and all the LOBS.

Once you have your files, you will need to make sure you have an Oracle Client installed, or have the full Instant Client.

This will give you access to SQL*Loader.

It's a command-line utility, no GUI. It works much like SQL*Plus does. You'll want to make sure your Oracle ENV is setup so you can start it up and connect.

But.

Everything you need is in the ZIP that SQLDev put together for you, the biggest piece is the .ctl (control file).

Docs

sqlldr scott CONTROL=ulcase1.ctl ulcase1.log

'scott' is the database username, it'll prompt you for a password. You'll subsitute the ulcase1.ctl for the ctl file you got from SQLDev. The log bit is optional, but IMPORTANT.

By the way, this should run FAST.

If you're running this on your pc, your connect string will be more like

sqlldr hr@server:port/service 

Is there a way to export a table with large CLOB entries to a flat file?

I managed to solve it by exporting the table(s) as xml format and converting that file to a .tsv file for import to MSSQL using a small script. Had to keep track of field terminators and row terminators in the .tsv file, as the BULK INSERT statement in MSSQL prefers these parameters and they need to be consistent.

Dumping CLOB fields into files?

This pl/sql code should work in oracle 11g.
It dumps the text of the clobs into a directory with the title as filename.

BEGIN 
  FOR rec IN (
    select title, text 
    from mytable
  )
  LOOP 
    DBMS_XSLPROCESSOR.clob2file(rec.text, 'DUMP_SOURCES', rec.title ||'.txt'); 
  END LOOP;
END;

If DBMS_XSLPROCESSOR isn't available then you could replace DBMS_XSLPROCESSOR.clob2file with a procedure that uses UTL_FILE.

For example :

CREATE OR REPLACE PROCEDURE CLOB2FILE (
    clob_in IN CLOB,
    directory_name IN VARCHAR2,
    file_name IN VARCHAR2
)
IS
    file_handle UTL_FILE.FILE_TYPE;
    clob_part VARCHAR2(1024);
    clob_length NUMBER;
    offset NUMBER := 1;
BEGIN
    clob_length := LENGTH(clob_in);
    file_handle := UTL_FILE.FOPEN(directory_name, file_name, 'W');

    LOOP
        EXIT WHEN offset >= clob_length;
        clob_part := DBMS_LOB.SUBSTR (clob_in, 1024, offset);
        UTL_FILE.PUT(file_handle, clob_part);
        offset := offset + 1024;
    END LOOP;

    UTL_FILE.FFLUSH(file_handle);
    UTL_FILE.FCLOSE(file_handle);

EXCEPTION
    WHEN OTHERS THEN
        UTL_FILE.FCLOSE(file_handle);
        RAISE;

END;

Or perhaps replace DBMS_XSLPROCESSOR.clob2file with dbms_advisor.create_file.

---

Related Topics