Ask the Expert

Converting LONGRAW to BLOB

I am on Oracle 8.0.5 version. It is not an 8i database. I want to convert LONGRAW data into BLOB. I have written the following the code, but it gives me a PL/SQL numeric error on the dbms_lob.write statement. I really don't know how to solve this problem. My code:


Declare
lv_bytes_to_write  Number := 1000;
buffer_var         long raw;
offset_var         number := 1;
v_chunklength      Integer := 0;
v_cnt              Number := 0;
v_loops            Number := 0;
t_blob             blob;
BEGIN
  insert into tmp_blob values (32618,empty_blob());

for rec in (select drawing from fsp_viewport_drawings 
where dwg_index_no = 32618)
loop
  select tmp_dwg
    into t_blob
    from tmp_blob
   where index_no = 32618 for update;

  buffer_var := rec.drawing;
  v_chunklength := utl_raw.length(buffer_var);
  dbms_output.put_line('Chunk Length ' || to_char(v_chunklength));
  If v_chunklength > 1000 Then
     If Mod(v_chunklength,1000) = 0 Then
        v_loops := v_chunklength/1000;
     else
        v_loops := (v_chunklength/1000) + 1;
     End if;
  Else
     v_loops := 1;
  End if;
  dbms_output.put_line('Loops' || to_char(v_loops));
  dbms_output.put_line('Vcnt ' || to_char(v_cnt));
  loop
    IF v_cnt > v_loops  Then
       Exit;
    Else
       dbms_output.put_line('Before writing the data');
       DBMS_LOB.WRITE(t_blob, lv_bytes_to_write, offset_var, buffer_var);
       dbms_output.put_line('Vcnt inside loop' || to_char(v_cnt));
       offset_var := offset_var + lv_bytes_to_write;
    End if;
      v_cnt := v_cnt + 1;
  end loop;
end loop;
Exception
     When Others then
          dbms_output.put_line('Screwed up ' || SQLCODE ||'  '||SQLERRM);
          rollback;
End;


    Requires Free Membership to View

The following example is from Chapter 21 of the book Oracle8 PL/SQL Programming by Scott Urman (you'd pass in the query against your fsp_viewport_drawings table as the first parameter when you run it):

CREATE OR REPLACE PROCEDURE Long2Lob(    
-- Uses DBMS_SQL to select a LONG column identified by p_LongQuery, and    
-- returns it in p_CLob.    
p_LongQuery IN VARCHAR2,    
p_CLob IN OUT CLOB) 
AS      
   c_ChunkSize CONSTANT INTEGER := 100;      
   v_CursorID INTEGER;    
   v_RC INTEGER;    
   v_Chunk VARCHAR2(100);    
   v_ChunkLength INTEGER;    
   v_Offset INTEGER := 0;  
BEGIN    
-- Open the cursor, define, execute, and fetch.    
   v_CursorID := DBMS_SQL.OPEN_CURSOR;    
   DBMS_SQL.PARSE(v_CursorID, p_LongQuery, DBMS_SQL.V7);    
   DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1);    
   v_RC := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);      
   -- Loop over the LONG, fetching c_ChunkSize characters at a time from    
   -- the LONG and adding them to the LOB.    
   LOOP      
      DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, c_ChunkSize, v_Offset, v_Chunk, v_ChunkLength);
      DBMS_LOB.WRITE(p_CLob, v_ChunkLength, v_Offset + 1, v_Chunk);      
      IF v_ChunkLength < c_ChunkSize THEN        
         EXIT;      
      ELSE        
         v_Offset := v_Offset + v_ChunkLength;      
      END IF;    
   END LOOP;      
   DBMS_SQL.CLOSE_CURSOR(v_CursorID);  
EXCEPTION    
WHEN OTHERS THEN     
-- Clean up, and reraise the error.      
   DBMS_SQL.CLOSE_CURSOR(v_CursorID);      
   RAISE;  
END Long2Lob;  
/
This code worked fine in my quick tests. Hope it helps.

For More Information


This was first published in June 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: