Flushing CLOB fields to files?

Question

Say you have a table:

Column_name | data_type Title | Varchar2 Text | CLOB 

with multiple lines:

 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 request that it output files such as

 SomeUnkownMovie.txt FredMercuryBio.txt 

(and ofc, with their respective texts inside)

I believe this should be a fairly simple sqlplus script .. although I'm just not that one :(

thanks!

Answer 1

This pl / sql code should work in oracle 11g. It flushes the text of the clobs in the directory with the name as the file name.

 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 is not available, you can replace DBMS_XSLPROCESSOR.clob2file with a procedure using UTL_FILE. For instance:

 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 the DBMS_XSLPROCESSOR.clob2 file with the dbms_advisor.create_file file.

Answer 2

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

If you are trying to generate files in the file system of a database server, there is an example of exporting a CLOB to a file in another StackOverflow stream based on Tim Hall LOB export examples (at the moment, the Tim site does not work).

If you are trying to generate files on the client file system, this will be associated with much more complex SQLPlus scripts. You would like to do something like query a table and use the data to dynamically create one SQLPlus script for each file that you would like to generate and then dynamically call these scripts. You would really be pushing the SQL * Plus scripting capabilities so that it is not the architecture that I usually defended, but I believe that it can be done.

If you need to generate files in the client file system, I usually 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 website. I would like to write a small Java utility that runs on the client and exports data, rather than trying to add too much logic to the SQLPlus script.