Q

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;


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close