soujii
asked on
Write clob data into a text file using utl_file if more than 32 Kb
Hi,
When I am trying to write clob data more than 32 KB into a text file using utl_file, I am facing issues.
I ma getting an error:
Error: ORA-29285: file write error
ORA-06512: at line 28
Can you please help.
When I am trying to write clob data more than 32 KB into a text file using utl_file, I am facing issues.
I ma getting an error:
Error: ORA-29285: file write error
ORA-06512: at line 28
Can you please help.
declare
t_out_file UTL_FILE.file_type;
t_buffer VARCHAR2(32767);
t_amount BINARY_INTEGER := 1000;
t_pos INTEGER := 1;
t_clob_len INTEGER;
P_DATA CLOB;
P_DIR VARCHAR2(100) := 'D:/oracle/test';
P_FILE VARCHAR2(100) := 'test1.csv';
BEGIN
select clb_col into P_DATA from tab1 where rownum = 1;
t_clob_len := DBMS_LOB.GetLength(p_data);
t_out_file := UTL_FILE.fOpen(p_dir,p_file, 'W', 32767);
dbms_output.put_line(' length : '||t_clob_len);
WHILE t_pos < t_clob_len LOOP
dbms_output.put_line(' t_pos before : '||t_pos);
DBMS_LOB.Read(p_data, t_amount, t_pos, t_buffer);
UTL_FILE.Put(t_out_file, t_buffer);
UTL_FILE.fflush(t_out_file);
dbms_output.put_line(' t_pos after : '||t_pos);
t_pos := t_pos + t_amount;
END LOOP;
dbms_output.put_line('completed writing');
UTL_FILE.fClose(t_out_file);
EXCEPTION
WHEN OTHERS THEN
IF(UTL_FILE.Is_Open(t_out_file))THEN
UTL_FILE.fClose(t_out_file);
END IF;
RAISE;
END;
also DBMS_LOB package can do the job.
Delete the section "convert the data to rawtohex format"
because the example is constructed for BLOBs:
How to save a BLOB to a file on disk in PL/SQL
From: Thomas Kyte
Use DBMS_LOB to read from the BLOB
You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.
There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.
Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES
Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure.
-------------------------- ---------- -
DECLARE
i1 BLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i3 NUMBER := 10000;
BEGIN
-- get the blob locator
SELECT c2
INTO i1
FROM lob_tab
WHERE c1 = 2;
-- find the length of the blob column
len := dbms_lob.getlength(i1);
dbms_output.put_line('Colu mn Length: ' || TO_CHAR(len));
-- Read 10000 bytes at a time
i2 := 1;
IF len < 10000 THEN
-- If the col length is < 10000
dbms_lob.read(i1,len,i2,my _vr);
outputstring('p:\bfiles\ra vi.bmp',
rawtohex(my_vr),'wb',2*len );
-- You have to convert the data to rawtohex format.
-- Directly sending the buffer
-- data will not work
-- That is the reason why we are sending the length as
-- the double the size of the data read
dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
ELSE
-- If the col length is > 10000
dbms_lob.read(i1,i3,i2,my_ vr);
outputstring('p:\bfiles\ra vi.bmp',
rawtohex(my_vr),'wb',2*i3) ;
dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
END IF;
i2 := i2 + 10000;
WHILE (i2 < len )
LOOP
-- loop till entire data is fetched
dbms_lob.read(i1,i3,i2,my_ vr);
dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) ||
' Bytes ');
outputstring('p:\bfiles\ra vi.bmp',
rawtohex(my_vr),'ab',2*i3) ;
i2 := i2 + 10000 ;
END LOOP;
END;
/
Delete the section "convert the data to rawtohex format"
because the example is constructed for BLOBs:
How to save a BLOB to a file on disk in PL/SQL
From: Thomas Kyte
Use DBMS_LOB to read from the BLOB
You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.
There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.
Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES
Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure.
--------------------------
DECLARE
i1 BLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i3 NUMBER := 10000;
BEGIN
-- get the blob locator
SELECT c2
INTO i1
FROM lob_tab
WHERE c1 = 2;
-- find the length of the blob column
len := dbms_lob.getlength(i1);
dbms_output.put_line('Colu
-- Read 10000 bytes at a time
i2 := 1;
IF len < 10000 THEN
-- If the col length is < 10000
dbms_lob.read(i1,len,i2,my
outputstring('p:\bfiles\ra
rawtohex(my_vr),'wb',2*len
-- You have to convert the data to rawtohex format.
-- Directly sending the buffer
-- data will not work
-- That is the reason why we are sending the length as
-- the double the size of the data read
dbms_output.put_line('Read
ELSE
-- If the col length is > 10000
dbms_lob.read(i1,i3,i2,my_
outputstring('p:\bfiles\ra
rawtohex(my_vr),'wb',2*i3)
dbms_output.put_line('Read
END IF;
i2 := i2 + 10000;
WHILE (i2 < len )
LOOP
-- loop till entire data is fetched
dbms_lob.read(i1,i3,i2,my_
dbms_output.put_line('Read
' Bytes ');
outputstring('p:\bfiles\ra
rawtohex(my_vr),'ab',2*i3)
i2 := i2 + 10000 ;
END LOOP;
END;
/
ASKER
THanks! will check the solution
ASKER
Hi,
I tried with your 1st solution.It's giving the sam error.
But, I have modified my code by keeping utl_file.put_line instead of utl_ftp.ptu.It started working if it is more than 32 KB
I tried with your 1st solution.It's giving the sam error.
But, I have modified my code by keeping utl_file.put_line instead of utl_ftp.ptu.It started working if it is more than 32 KB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE OR REPLACE PROCEDURE dpr_clobToFile
( p_fileName IN VARCHAR2,
p_dir IN VARCHAR2,
p_clob IN CLOB ) IS
c_amount CONSTANT BINARY_INTEGER := 32767;
l_buffer VARCHAR2(32767);
l_chr10 PLS_INTEGER;
l_clobLen PLS_INTEGER;
l_fHandler UTL_FILE.FILE_TYPE;
l_pos PLS_INTEGER := 1;
BEGIN
l_clobLen := DBMS_LOB.GETLENGTH(p_clob)
l_fHandler := UTL_FILE.FOPEN(p_dir, p_fileName,'W',c_amount);
WHILE l_pos < l_clobLen LOOP
l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);
EXIT WHEN l_buffer IS NULL;
l_chr10 := INSTR(l_buffer,CHR(10),-1)
IF l_chr10 != 0 THEN
l_buffer := SUBSTR(l_buffer,1,l_chr10-
END IF;
UTL_FILE.PUT_LINE(l_fHandl
l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c
END LOOP;
UTL_FILE.FCLOSE(l_fHandler
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(l_fHandle
UTL_FILE.FCLOSE(l_fHandler
END IF;
RAISE;
END;
/