• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Need to Unload CLOB Column to an OS File

I have several database tables that have CLOB columns, and have a need to unload the CLOB column to a file, so that the file can be modified and then re-inserted.  Not extremely familiar with CLOBS.  I hear that the DBMS_LOBS may help.  Is there an efficient method to essential export the CLOB column?
0
sdruss
Asked:
sdruss
  • 13
  • 11
  • 9
2 Solutions
 
sdstuberCommented:
This should do it...



CREATE OR REPLACE PROCEDURE clob2file(p_clob IN OUT NOCOPY CLOB, p_directory IN VARCHAR2, p_file IN VARCHAR2)
    IS
        v_file                   UTL_FILE.file_type;
        c_chunk_limit   CONSTANT INTEGER := c_vc2limit;
        v_lob_length             INTEGER := DBMS_LOB.getlength(p_clob);
        v_lob_index              INTEGER := 1;
        v_chunk                  VARCHAR2(32767);
    BEGIN
        v_file  := UTL_FILE.fopen(p_directory, p_file, 'w', 32767);

        WHILE v_lob_index <= v_lob_length
        LOOP
            v_chunk      := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_lob_index);
            v_lob_index  := v_lob_index + c_chunk_limit;
            UTL_FILE.put(v_file, v_chunk);
            UTL_FILE.fflush(v_file);
        END LOOP;

        UTL_FILE.fclose(v_file);
    END clob2file;
0
 
slightwv (䄆 Netminder) Commented:
Following a similar procedure to the one above,  here is the load piece if you need it.

You can tweak parameters to make it more generic if you want.

drop table bob purge;
create table bob (myclob clob);

create or replace directory some_dir as 'C:\';

CREATE OR REPLACE PROCEDURE file2clob(p_directory IN VARCHAR2, p_file IN VARCHAR2)
    IS
BEGIN

DECLARE

	v_bfile			 bfile := BFILENAME( p_directory , p_file);
	v_lob			 clob;


	src_offset  number := 1;
	dst_offset  number := 1;
	cs_id       number := NLS_CHARSET_ID('UTF8'); /* 998 */
	lang_ctx    number := dbms_lob.default_lang_ctx;
	warning     number;

	BEGIN


	insert into bob(myclob )
		values(empty_clob()) returning myclob into v_lob;

	DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
	dbms_lob.LOADCLOBFROMFILE(v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);
	DBMS_LOB.CLOSE(v_bfile);
	commit;
	END;

END;
/

show errors


exec file2clob('SOME_DIR','test.txt');

Open in new window

0
 
sdstuberCommented:
For easier reuse, you might want to use a function approach



CREATE OR REPLACE    FUNCTION file2clob(p_directory IN VARCHAR2, p_file IN VARCHAR2)
        RETURN CLOB
    IS
        v_bfile   BFILE := BFILENAME(p_directory, p_file);
        v_clob    CLOB;
    BEGIN
        DBMS_LOB.createtemporary(v_clob, TRUE);
        DBMS_LOB.open(v_bfile, DBMS_LOB.lob_readonly);
        DBMS_LOB.loadfromfile(v_clob, v_bfile, DBMS_LOB.lobmaxsize);

        RETURN v_clob;
    END file2clob;
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
sdrussAuthor Commented:
How do you call the above 'clob2file' stored procedure.  For instance with a table that contains several rows with CLOB columns -how do indicate which row?  Assume the call would be something like this:

    execute clob2file(mytable.mycolumn, 'dir_foo', 'file_bar');

If my table has 60+ rows, how do I indicate which row with the clob?
0
 
sdstuberCommented:
something like this...  I've used rownum so each clob gets written to a distinct file

use a where clause on the query to control which clob rows are returned


begin
     for x in (select rownum rn, mycolumn from mytable ) loop
               clob2file(x.mycolumn, 'dir_foo', 'file_bar'||x.rn);
     end loop;
end;
0
 
slightwv (䄆 Netminder) Commented:
>>How do you call the above 'clob2file' stored procedure

Pass it a CLOB not a column.  You could always tweak it to pass in a unique id for the row like a rowid.

But as is, write a simple pl/sql script to call it:

begin
for i in (select someid, myclob from tableName where someid in (1,2,3,4,5)) loop
      clob2file(i.myclob, 'SOME_DB_DIRECTORY', 'file_' || i.someid));
end loop;
end;
/
0
 
sdrussAuthor Commented:
Have trouble with first stored procedure clob2file.  Having undefined issues with:  c_vc2limit.  How exactly does this variable definition and initialization work.  

   c_chunk_limit   CONSTANT INTEGER := c_vc2limit;
0
 
slightwv (䄆 Netminder) Commented:
It isn't my code but try changing:
c_chunk_limit   CONSTANT INTEGER := c_vc2limit;

to:
c_chunk_limit   CONSTANT INTEGER := 32767;
0
 
sdstuberCommented:
yes, sorry about that,  I pulled my procedure out of one of my packages that does other LOB operations.

slightwv is correct the constant should be 32767
0
 
sdrussAuthor Commented:
Thanks, setting the constant fixed the problem.  No on to executing the stored procedure problems.  Getting this error after executing like this:

   SQL> exc Utils.clob2file(mytable.clobcolumn, '/tmp', 'foo.bar');

Error at line1:
ORA-06550, line 1 column 27:
PLS-00363: expression 'mytable.clobcolumn cannot be used as an assignement target
ORA-06550: line1, column 7:
PL/SQL:  Statement ignored
0
 
sdstuberCommented:
you're still trying to use it like you did originally, that won't work

put it in a block as shown above

http:#a38835769
0
 
sdrussAuthor Commented:
Yes, the block works correctly as demonstrated.  Thanks!  However, I was wrong don't want each record as a seperate file.  Really I want the whole table (e.g. all the CLOBs in each row) written to a single file.  How do I do this?
0
 
sdstuberCommented:
change the clob2file to append instead of write


CREATE OR REPLACE PROCEDURE clob2file(p_clob IN OUT NOCOPY CLOB, p_directory IN VARCHAR2, p_file IN VARCHAR2)
    IS

        v_file                   UTL_FILE.file_type;
        c_chunk_limit   CONSTANT INTEGER := 32767;
        v_lob_length             INTEGER := DBMS_LOB.getlength(p_clob);
        v_lob_index              INTEGER := 1;
        v_chunk                  VARCHAR2(32767);
    BEGIN
        v_file  := UTL_FILE.fopen(p_directory, p_file, 'a', 32767);

        WHILE v_lob_index <= v_lob_length
        LOOP
            v_chunk      := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_lob_index);
            v_lob_index  := v_lob_index + c_chunk_limit;
            UTL_FILE.put(v_file, v_chunk);
            UTL_FILE.fflush(v_file);
        END LOOP;

        UTL_FILE.fclose(v_file);
    END clob2file;

and in the block, don't change the file name on each run



begin
     for x in (select  mycolumn from mytable ) loop
               clob2file(x.mycolumn, 'dir_foo', 'file_bar');
     end loop;
end;
0
 
slightwv (䄆 Netminder) Commented:
>>written to a single file.

You mentioned in your original question that you wanted to eventually load them back into a table.

If you have many rows written to a single file, how do you plan on doing this?

For example, given the following setup, and write the results to a single file, how do you load it back?

create table tab1(id number, text clob);
insert into tab1 values(1,'Hello');
insert into tab1 values(1,'World');

If you write the 'text' column to a single file you get:
HelloWorld

or line separated:
Hello
World

How do you plan on reloading this?
0
 
sdrussAuthor Commented:
sdstuber:  Looks like this not get the whole table:

begin
     for x in (select  mycolumn from mytable ) loop
               clob2file(x.mycolumn, 'dir_foo', 'file_bar');
     end loop;
end;

slightvw:  apparently reloading is taken care of by another tool
0
 
sdstuberCommented:
>>>   Looks like this not get the whole table:

it's not intended to.  You asked to write a clob column,  that's what the procedure and the loop to invoke that procedure does.


If you want to add new requirements, those should be a new question
0
 
sdrussAuthor Commented:
Okay, how should I "ask" the question properly.  Please let me know, then I will close this question, and open a new question.  Absolutely appreciate the help.  Thanks!
0
 
sdstuberCommented:
In your new question
provide a sample table description and example of what you'd like the output to be.

for example:

MY_TABLE (
my_id    integer,
my_name varchar2(100),
my_date  date
my_life_story  clob);

expected output...

1,John Doe,2000-01-23 01:23:45,John was born in a small town and grew up to be a DBA and ruled the world.
2,Jane Doe,2013-02-01 10:48:30,Jane is the daughter of famous DBA/world ruler John Doe.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm not sure I understand what you want so I cannot tell you how to ask your question.

From your last post, I'm assuming you are wanting to extract an entire table into some sort of delimited format and the table contains a CLOB field.

If this is accurate, I will tell you now that this will be a very difficult question to answer.

For example, common extracts are comma delimited with optional double quotes (Excel like).

The problem you have is if your data can actually contain these characters.

A typical extract looks like:
"1","A","2"

This works fine until a table has the following value in a column:
Moe, "Larry", Curly

Just make sure you have enough of the actual requirements so when we provide a solution, it is an accurate one.
0
 
sdrussAuthor Commented:
sdstuber's table is an accurate table definition.  The data is in XML format, and I don't have any need for delimiters.  I want to extract the clobs in each row, to a single file.  Only want a single file for the whole table, that contains all of the CLOBS.  Current table has 100 rows.  If this is a new question - please help me formulate the question properly/better.  Thanks.
0
 
sdstuberCommented:
start a new question and post some sample data with expected results.

you don't need to use real data, I made mine up and it doesn't need to be a lot, just post enough to be representative of what you're trying to get.


if what you want is what I think you might want, the answer might be relatively easy, but it won't look anything at like the answer shown in this question.  I have a few guesses but you need to tell/show us.

Showing with data is almost always better than trying to describe.
0
 
slightwv (䄆 Netminder) Commented:
>>sdstuber's table is an accurate table definition.  The data is in XML format, and I don't have any need for delimiters

Using sdstubers ecample, in your planned output file how do you plan on writing out my_id and my_name in such a way that it can be reloaded later?

His expected results are comma separated.  This is a form of delimiter.

This breaks if his my_name column is "John, Doe".

If your data can have a comma in it, you cannot generate a CSV w/o some extra requirements and thought.
0
 
sdrussAuthor Commented:
As it relates to sdstuber's table example - don't want to include my_id, my_name, etc.  Only want my_life_story CLOB column.
0
 
sdstuberCommented:
then you already have your answer above.


if it doesn't work, please post exactly what you tried and exactly what it did wrong, what it added or what it left out
0
 
slightwv (䄆 Netminder) Commented:
I agree that you can extract the clob column for all rows in the table using what has been posted here.

If you want the procedure rewritten so you pass in a table/column name and it does everything for you, ask, we can do that.

I'm still concerned that if you only extract the clob, loading back in might be problematic but as long as you are sure you have that covered, I'll not worry about it any more.
0
 
sdrussAuthor Commented:
Would it be okay to "delete this question" and then immediately ask the proper question in private mode?  Believe only sdstuber and slightwv have responded with solutions.
0
 
slightwv (䄆 Netminder) Commented:
A private question means it is not 'supposed' to be indexed by search engines.  It is still visible to other members.

I say 'not supposed to be' because depending on how EE protects them, some search engines may still be able to see it.

Why are you thinking you want to make it private?

I suppose you can delete this but if you are basically going to ask the same question again, I really don't see the need.

sdstuber and myself are both Zone Advisors on the site and can help you out in any way we can with the exception of helping you outside of the site.

If you dummy up some sample data and provide expected results, we can help.

A simple dummy test case is pretty simple to create.

I understand you have XML and some other columns.

For example, it took about 1 minute to come up with a sample data:

drop table tab1 purge;
create table tab1(id number, mydata clob);

insert into tab1 values(1, '<a>Hello</a>');
insert into tab1 values(2, '<b>World</b>');
commit;

Open in new window


Now all we need is that you want.

If it a text file called 'q.txt' with the contents of:
<a>Hello</a><b>World</b>

That is pretty straight forward.

If you want a procedure where you pass in: table_name, file_name and the directory is static.  That is also easy (I'll work on a test case and post it soon based on the above table).

We just need to know exactly what you want.
0
 
slightwv (䄆 Netminder) Commented:
Here is the test that takes a table_name and column_name and dumps the column to a file.

I based it on the clob2file procedure posted be sdstuber.

Let us know what else you need

drop table tab1 purge;
create table tab1(id number, mydata clob);

insert into tab1 values(1, '<a>Hello</a>');
insert into tab1 values(2, '<b>World</b>');
commit;

create or replace directory mydir as 'C:\';

CREATE OR REPLACE PROCEDURE column2file(p_table_name IN varchar2, p_column_name in varchar2, p_file in varchar2)
IS
	v_file		UTL_FILE.file_type;
	c_chunk_limit	CONSTANT INTEGER := 32767;
	v_lob_length	INTEGER;
	v_lob_index	INTEGER;
	v_chunk		VARCHAR2(32767);
	v_clob		clob;

	mycursor	sys_refcursor;
BEGIN
	v_file  := UTL_FILE.fopen('MYDIR', p_file, 'w', 32767);

	open mycursor for 'select ' || p_column_name || ' from ' || p_table_name;

	loop
	fetch mycursor into v_clob;
	exit when mycursor%NOTFOUND;

	v_lob_index := 1;
	v_lob_length := dbms_lob.getlength(v_clob);
		WHILE v_lob_index <= v_lob_length LOOP
			v_chunk      := DBMS_LOB.SUBSTR(v_clob, c_chunk_limit, v_lob_index);
			v_lob_index  := v_lob_index + c_chunk_limit;
			UTL_FILE.put(v_file, v_chunk);
			UTL_FILE.fflush(v_file);
		END LOOP;
		--if you want a cr/lf between rows
		UTL_FILE.put(v_file, utl_tcp.crlf );
	end loop;
	close mycursor;

	UTL_FILE.fclose(v_file);
END column2file; 
/

show errors

exec column2file('TAB1','MYDATA','q.txt');

Open in new window

0
 
sdrussAuthor Commented:
slightwv - excellent, looks good.  I'll need to compile and run simple test tomorrow.  Thanks.
0
 
sdrussAuthor Commented:
Did not get a chance to implement and test thus far.  However, pretty sure this fits the bill.  Possibly may need to open-new again.  Thanks to all!
0
 
sdstuberCommented:
wouldn't a split be appropriate since the accepted answer is basically just a little tweak to  the code I posted originally?
0
 
sdrussAuthor Commented:
Agree, that is what I attempted to do.  My apology to both advisers!  Interface is not smooth.  I will attempt to do again.  Absolutely agree both slighwv and sdstuber should share equally in the points (250 each).
0
 
sdrussAuthor Commented:
My intention is to split the points for sdstuber and slightwv
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 13
  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now