Solved

Need to Unload CLOB Column to an OS File

Posted on 2013-01-29
34
784 Views
Last Modified: 2013-02-02
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
Comment
Question by:sdruss
  • 13
  • 11
  • 9
34 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 38833756
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38835113
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38835131
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
 
LVL 1

Author Comment

by:sdruss
ID: 38835662
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38835769
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38835775
>>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
 
LVL 1

Author Comment

by:sdruss
ID: 38838105
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
 
LVL 76

Expert Comment

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

to:
c_chunk_limit   CONSTANT INTEGER := 32767;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38838318
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
 
LVL 1

Author Comment

by:sdruss
ID: 38840256
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38840762
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
 
LVL 1

Author Comment

by:sdruss
ID: 38841899
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38842100
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38842287
>>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
 
LVL 1

Author Comment

by:sdruss
ID: 38842482
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38842542
>>>   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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:sdruss
ID: 38844040
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38844068
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38844081
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
 
LVL 1

Author Comment

by:sdruss
ID: 38844136
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38844142
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38844524
>>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
 
LVL 1

Author Comment

by:sdruss
ID: 38844962
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38844985
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38845305
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
 
LVL 1

Author Comment

by:sdruss
ID: 38845694
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38845777
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 38845800
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
 
LVL 1

Author Comment

by:sdruss
ID: 38845960
slightwv - excellent, looks good.  I'll need to compile and run simple test tomorrow.  Thanks.
0
 
LVL 1

Author Comment

by:sdruss
ID: 38847079
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38847133
wouldn't a split be appropriate since the accepted answer is basically just a little tweak to  the code I posted originally?
0
 
LVL 1

Author Comment

by:sdruss
ID: 38848011
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
 
LVL 1

Author Closing Comment

by:sdruss
ID: 38848015
My intention is to split the points for sdstuber and slightwv
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now