writing a clob column into a flat file

I am trying to write from a oracle table having 1 column defined as CLOB to a  flat file and I am using the following code..

the code works only for rownum =1 and if I change this to < 9 ( I have 8 rows) it does not work..

Can any experts jump in and solve this.. Thanks in advance..Any help on this will be appreciated.!!
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_clob CLOB;
v_buffer VARCHAR2(32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
SELECT col1
INTO v_clob
FROM tab1
WHERE rownum = 1;
v_file := UTL_FILE.FOPEN('DOCUMENTS', 'Sample2.txt', 'w', 32767);
LOOP
Dbms_Lob.Read (v_clob, v_amount, v_pos, v_buffer);
UTL_FILE.PUT(v_file, v_buffer);
v_pos := v_pos + v_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(v_file);
END;

Open in new window

mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
Your LOOP does not seem to have any EXIT condition. Did u miss it?
0
mahjagAuthor Commented:
No  - when should I exit then?
0
mahjagAuthor Commented:
You are correct, I tried to put exit when but this did not work so I change back to rownum = 1, can you please edit and post the code so I can try,, Thanks a lot again!!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jinesh KamdarCommented:
Try this.
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_clob CLOB;
v_buffer VARCHAR2(32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
v_file := UTL_FILE.FOPEN('DOCUMENTS', 'Sample2.txt', 'w', 32767);
FOR i IN (SELECT col1 FROM tab1) LOOP
v_pos := 1;
LOOP
BEGIN
Dbms_Lob.Read (i.col1, v_amount, v_pos, v_buffer);
UTL_FILE.PUT(v_file, v_buffer);
v_pos := v_pos + v_amount;
EXCEPTION
WHEN OTHERS THEN
     EXIT;
END;
END LOOP;
END LOOP;
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(v_file);
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mahjagAuthor Commented:
Thanks for the response today..

one final questin for the day - I know it is too late for you..

how would you call multiple oracle procedure from unix prompt

sqlplus username/password@instance
execute proc1 ()
execute proc2()

I only got first proc to work and not the other..

I will post this question separately if you think then you will earn more points..
0
mahjagAuthor Commented:
Dear Jinesh Kamdar

I have a problem that the file exported does not have a carriage return, can you look at your code and add carriage return and let me know..

Thanks and Regards
0
Jinesh KamdarCommented:
Glad to be of help :)
0
mahjagAuthor Commented:
if you could answer the addition of carriage return in your code to load the clob file into flat file that will be great.. I was actually planning for a demo today morning and found that I am running late..
0
Jinesh KamdarCommented:
My apologies for not reading all of ur comments.

>> how would you call multiple oracle procedure from unix prompt

Try this.

sqlplus username/password@instance -s << EOF
execute proc1 ()
execute proc2()
EOF

>> the file exported does not have a carriage return

Try below.
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_clob CLOB;
v_buffer VARCHAR2(32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
v_file := UTL_FILE.FOPEN('DOCUMENTS', 'Sample2.txt', 'w', 32767);
FOR i IN (SELECT col1 FROM tab1) LOOP
v_pos := 1;
LOOP
BEGIN
Dbms_Lob.Read (i.col1, v_amount, v_pos, v_buffer);
UTL_FILE.PUT(v_file, v_buffer);
v_pos := v_pos + v_amount;
EXCEPTION
WHEN OTHERS THEN
     EXIT;
END;
END LOOP;
UTL_FILE.NEW_LINE; ---> Added this line.
END LOOP;
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(v_file);
END;

Open in new window

0
mahjagAuthor Commented:
Thanks for looking this thru - when I add the new line I get error in compilation..

PLS-00306: wrong number or types of arguments in call to 'NEW_LINE'

Sorry to bother you, I think it need arguments..
0
mahjagAuthor Commented:
Ignore my comments I got that to work by putting in the arguments v_file,1.. thanks again for your support
0
Jinesh KamdarCommented:
Glad to be of help :)
0
mahjagAuthor Commented:
Hi Jinesh Kamdar

You have been awesome in answering questions for me, I hate to reopen this question again as it did not sever what I need.

when writing to flat file is there a way I can take carriage return out

this is what I get now as a flat file
select col1, col2 <carriage retun>
from <tablename> <CR>
where .... <CR>
and so on......

I want like this
select col1.,col2 from tablename where ... and ...
all in one line without carriage return..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.