jimrider1
asked on
How do I write an NCLOB object from Oracle 9iR2 database to the file system using PL/SQL (the NCLOB can contain word docs, pdf, excel)
How do I write a NCLOB field to a system file Using PL/SQL. The db is Oracle 9iR2. The NCLOB can contain ms word dos, pdf, xls. I have tried to write to a file but, the file will not open with the associated application when in windows. See code snippet. When I open the file in word pad, most of the data is not readable. Thanks, Jim
DECLARE
v_blob blob;
v_offset integer;
v_buffer_varchar varchar2(32000);
v_buffer_raw raw(32000);
v_buffer_size binary_integer := 32000;
p_clob_in CLOB;
nclob_in NCLOB;
file_ref utl_file.file_type;
lob_size number;
raw_max_size constant number := 32767;
buffer raw(32767);
buffer_offset number := 1; -- Position in stream
buffer_length number; -- Amount of data to read and write
vopen_mode VARCHAR2(16) := 'W';
-- clob --
c_clob_loc NCLOB;
c_buffer VARCHAR2(32767);
c_buffer_size BINARY_INTEGER := 32767;
c_amount BINARY_INTEGER;
c_offset integer := 1;
c_file_handle utl_file.file_type;
--blob --
i1 BLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i3 NUMBER := 10000;
ext_blob_hdl utl_file.file_type;
begin
-- convert NCLOB to CLOB --
Select to_clob(Mac2kFile.FILEBLOB)
INTO p_clob_in From MACSYS.Mac2kFile Mac2kFile
Where FileIAAKey = 493790;
dbms_output.put_line('CLOB Variable Length: ' || to_char(DBMS_LOB.GETLENGTH(p_clob_in)));
-- write CLOB file --
c_file_handle := UTL_FILE.FOPEN('DIROBJ_DATA_EMAIL', 'clob.doc', 'w', 32760);
c_amount := c_buffer_size;
c_offset := 1;
WHILE c_amount >= c_buffer_size
LOOP
dbms_lob.READ(p_clob_in,c_amount,c_offset,c_buffer);
c_offset := c_offset + c_amount;
UTL_FILE.PUT(c_file_handle,c_buffer);
UTL_FILE.FFLUSH(c_file_handle);
END LOOP;
UTL_FILE.FCLOSE(c_file_handle);
-- create BLOB from CLOB --
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
v_offset := 1;
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / v_buffer_size)
loop
dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
v_offset := v_offset + v_buffer_size;
end loop;
dbms_output.put_line('BLOB Variable Length: ' || to_char(DBMS_LOB.GETLENGTH(v_blob)));
-- write out BLOB to external file --
-- find the length of the blob column
len := dbms_lob.getlength(v_blob);
ext_blob_hdl := UTL_FILE.FOPEN('DIROBJ_DATA_EMAIL', 'nclob5.doc', 'w', 32760);
dbms_output.put_line('Column 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(v_blob,len,i2,my_vr);
utl_file.put_raw(ext_blob_hdl,my_vr,true);
--outputstring('/ora_apps/PROD/MACSYS/DATA/EMAIL/blob4.doc',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(v_blob,i3,i2,my_vr);
utl_file.put_raw(ext_blob_hdl,my_vr,true);
--outputstring('/ora_apps/PROD/MACSYS/DATA/EMAIL/blob4.doc',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(v_blob,i3,i2,my_vr);
dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) || ' Bytes ');
utl_file.put_raw(ext_blob_hdl,my_vr,true);
--outputstring('/ora_apps/PROD/MACSYS/DATA/EMAIL/blob4.doc',rawtohex(my_vr),'ab',2*i3);
i2 := i2 + 10000 ;
END LOOP;
utl_file.fclose(ext_blob_hdl);
END;
/
ASKER
When I just leave the data as an NCLOB - I get the following error message
Error on line 0
DECLARE
i1 NCLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 25
for the code snippet below
Error on line 0
DECLARE
i1 NCLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 25
for the code snippet below
DECLARE
i1 NCLOB;
len NUMBER;
my_vr RAW(10000);
i2 NUMBER;
i3 NUMBER := 10000;
ext_blob_hdl utl_file.file_type;
begin
Select Mac2kFile.FILEBLOB
INTO i1 From MACSYS.Mac2kFile Mac2kFile
Where FileIAAKey = 493790;
dbms_output.put_line('CLOB Variable Length: ' || to_char(DBMS_LOB.GETLENGTH(i1)));
len := dbms_lob.getlength(i1);
ext_blob_hdl := UTL_FILE.FOPEN('DIROBJ_DATA_EMAIL', 'nclob5.doc', 'w', 32760);
dbms_output.put_line('Column Length: ' || TO_CHAR(len));
-- Read 10000 bytes at a time
i2 := 1;
IF len < 10000 THEN
dbms_lob.read(i1,len,i2,my_vr);
utl_file.put_raw(ext_blob_hdl,my_vr,true);
dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
ELSE
dbms_lob.read(i1,i3,i2,my_vr);
utl_file.put_raw(ext_blob_hdl,my_vr,true);
dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
END IF;
i2 := i2 + 10000;
WHILE (i2 < len )
LOOP
dbms_lob.read(i1,i3,i2,my_vr);
dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) || ' Bytes ');
utl_file.put_raw(ext_blob_hdl,my_vr,true);
i2 := i2 + 10000 ;
END LOOP;
utl_file.fclose(ext_blob_hdl);
END;
/
Mac2kFile.FILEBLOB is this an nclob? based on its name and its contents I assumed it was a BLOB
ASKER
Yes, this is a NCLOB data type
CREATE TABLE MAC2KFILE
(
FILEIAAKEY NUMBER(12) NOT NULL,
FILEBLOB NCLOB NOT NULL,
FILESIZE NUMBER(12) NOT NULL,
IMAGECURRENT CHAR(1 BYTE) NOT NULL,
EXT CHAR(3 BYTE) NOT NULL,
MODBY NUMBER(12) NOT NULL,
MODDATE DATE NOT NULL,
MODPROG VARCHAR2(80 BYTE) NOT NULL,
CREATEBY NUMBER(12) NOT NULL,
CREATEDATE DATE NOT NULL,
CREATEPROG VARCHAR2(80 BYTE) NOT NULL
)
ASKER
I found out that the column used to be a BLOB then was converted to NCLOB.
hmmm, that's unfortunate, and odd since your data isn't character data, it's binary data. I would imagine you're corrupting your files as soon as your store them.
Pages 2-52 & 2-53 of the SQL reference show your problem.
You can't convert from an NCLOB back into raw data.
You need to use a BLOB
Pages 2-52 & 2-53 of the SQL reference show your problem.
You can't convert from an NCLOB back into raw data.
You need to use a BLOB
ASKER
Currently the data is being used by a VB application and the file (doc,xls,pdf) is being extracted successfully with the VB code. Do you have a link / url to "Pages 2-52 & 2-53 of the SQL reference"?
Thanks.
Thanks.
well, if the vb code can do it, then Oracle should be able to as well. I wonder if you're getting an implicit character conversion somehow in the pl/sql code that you're not getting in the vb code.
ASKER
Here is the VB code - can you please help translate to pl/sql? Thanks - Jim
Dim CurSize As Long, ChunkSize As Long
Dim nCnt As Long, FNum As Integer, CurChunk As String
'Set the size of each chunk
ChunkSize = Globs.BlobBlockSize '1048576
'Get a free file number
FNum = FreeFile
'Open the file
Open fName For Binary As #FNum
nCnt = 0
'Loop through all of the chunks
'Oracle does not return the size of columns > 64KB.
'We should loop until the length of our block is
'less than we asked for.
Do
CurChunk = fld.GetChunk(ChunkSize)
CurSize = Len(CurChunk) 'Get the length of the current chunk.
Put #FNum, , CurChunk 'Write chunk to file.
nCnt = nCnt + 1
Loop Until CurSize < ChunkSize
'Close the file.
Close FNum
Jim, did you find your solution?
why the delete request?
why the delete request?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok, I would have posted one, but I've been rebuilding my computer this past week, so I've had to cut back on my EE time. I've only been posting to code I could do in my head.
if you've got a java solution though, that's fine. Glad you got it working..
if you've got a java solution though, that's fine. Glad you got it working..
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
Vee_Mod
Community Support Moderator
That's probably where you're losing file integrity. Leave raw data as raw, then write it.