Dumping CLOB fields into files?

Question

Say you have the table:

Column_name | data_type
Title       | Varchar2
Text        | CLOB

with some rows:

SomeUnkownMovie | A long time ago in a galaxy far, far away....(long text ahead)
FredMercuryBio  | Awesomeness and stuff....(more long text)

Is there a way I could query that so it outputs files like

SomeUnkownMovie.txt
FredMercuryBio.txt

(and ofc, with their respective texts inside)

I reckon this should be a easy enough sqlplus script.. though I'm just not the one :(

thanks!

LukStorms · Accepted Answer · 2020-05-17T11:01:52.627

14

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.

edited May 17 '20 at 11:01

answered Sep 03 '14 at 09:15

LukStorms

28,916
5
31
45

My clob data have single quote in it, which gets converted to ' Any help?! – Stalin Gino Jul 01 '15 at 13:00
I've added a procedure clob2file to it that can replace it. – LukStorms Jul 09 '15 at 08:57
Btw, when I tested DBMS_XSLPROCESSOR.clob2file on a clob with a single quote, that quote wasn't replaced in the file. I'm suspecting that you're transforming an xmltype into clob before the export to file. – LukStorms Jul 09 '15 at 09:03
Yes, i take this to convert `SELECT extract(xmlagg(xmlelement(e,'column=' || column_name || '~')),'/E/text()').getclobval() AS def_text FROM user_tab_columns WHERE table_name = 'EMPLOYEE'` – Stalin Gino Jul 10 '15 at 05:53
1

You might want to lookup what DBMS_XMLGEN.CONVERT does. – LukStorms Jul 10 '15 at 07:38
I've seen different examples where the `UTL_FILE.FFLUSH` is inside of the loop and outside of the loop. Any idea what the benefit and con of each is? Should exception handling be added to this? – Matthew Moisen May 03 '17 at 17:54
1

@MatthewMoisen From what I understand, UTL_FILE.FFLUSH flushes the pending data identified by the file handle into the file. It's useful to use inside the loop if you want to read the file while it is still open. For example, to `tail` debugging/logging files while they are being written to. But if you just want to read the file after it's written, outside the loop is good enough. – LukStorms May 03 '17 at 20:17
@LukStorms Thanks. I've also seen examples where `UTL_FILE.FFLUSH` is included in the `EXCEPTION WHEN OTHERS` in addition to `FCLOSE`. I suspect that this is a bad idea as it could throw another exception prior to the invocation of `FCLOSE`. Any idea? – Matthew Moisen May 03 '17 at 22:13
1

@MatthewMoisen It's as you suspect. At that point something went wrong, so I didn't want FFLUSH to potentially raise a new less meaningfull exception. There was a problem with dumping the file, so why care about shaking that last spunk from the filebuffer? – LukStorms May 04 '17 at 07:23

score 6 · Answer 2 · edited May 23 '17 at 11:47

Are you trying to generate files on the database server file system? Or on the client file system?

If you are trying to generate files on the database server file system, there is an example of exporting a CLOB to a file in another StackOverflow thread that is based on Tim Hall's LOB export examples (Tim's site appears to be down at the moment).

If you're trying to generate files on the client file system, it would involve much more complex SQLPlus scripting. You'd be looking at doing something like querying the table and using the data to dynamically generate one SQLPlus script per file that you wanted to generate and then dynamically calling those scripts. You'd be really pushing SQL*Plus's scripting capabilities so that's not an architecture that I would generally advocate but I believe it could be done.

If you do need to generate files on the client file system, I'd generally prefer to use something other than SQLPlus. For example, there is an example of a small Java class that reads and writes CLOB and BLOB data to and from files on the AskTom site. I'd tend to write a small Java utility that ran on the client and exported the data rather than trying to put too much logic in SQLPlus scripts.

Oh.. all right.. Thought that would be easier. Well, guess it will be some ruby scripting fun ahead :) thanks! — filippo, Sep 07 '11 at 10:05

Dumping CLOB fields into files?

2 Answers2

Linked