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;
/

Open in new window

jimrider1Asked:
Who is Participating?
 
jimrider1Connect With a Mentor Author Commented:
going to use Java - could not get a plsql solution.
0
 
sdstuberCommented:
why do you turn your blob data into clob data?

That's probably where you're losing file integrity.  Leave raw data as raw, then write it.
0
 
jimrider1Author Commented:
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
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;
/

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
Mac2kFile.FILEBLOB    is this an nclob?  based on its name and its contents I assumed it was a BLOB
0
 
jimrider1Author Commented:
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
)

Open in new window

0
 
jimrider1Author Commented:
I found out that the column used to be a BLOB then was converted to NCLOB.
0
 
sdstuberCommented:
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
0
 
jimrider1Author Commented:
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.
0
 
sdstuberCommented:
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.
0
 
jimrider1Author Commented:
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

Open in new window

0
 
sdstuberCommented:
Jim,  did you find your solution?

why the delete request?
0
 
sdstuberCommented:
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..
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
All Courses

From novice to tech pro — start learning today.