How to Convert from Long Raw to BLOB/CLOB using PL/SQL

Convert from Long Raw to BLOB/CLOB using PL/SQL.

Goal and Aim

This article contains information on how to convert LONG/LONG RAW  with size equal or less 64kB to BLOB/CLOB using PL/SQL.

To convert LONG/LONG RAW with any size to BLOB/CLOB the SQL functions

• to_lob()

• to_clob()

can be used in Oracle 10gR2 (10.2.0.1) and higher.You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement - NOT in PLSQL as conversion function.

Solution

Example #1 [LONG/LONG RAW to BLOB]

Example #1 demonstrates how to convert a LONG column that is less than 64k into a BLOB using PL/SQL.

-- DROP table 
drop table traw; 
drop table tblob 
 
-- CREATE table 
create table traw (n1 number , l1 long raw); 
create table tblob (n1 number , l1 blob); 
  --- You can use  clob as well 
 
-- INSERT table 
begin 
  for i in 1..10 loop 
    insert into traw values (i,utl_raw.cast_to_raw(rpad(to_char(i),60,'&'))); 
    insert into tblob values (i,empty_blob()); 
  end loop; 
end; 
/ 
 
declare 
 lobloc blob; 
 buffer long raw(32000); 
 amount number ; 
 offset number := 1; 
begin 
 for rec in (select * from traw) loop 
   select l1 into lobloc from tblob where n1=rec.n1 for update; 
   buffer := rec.l1; 
   amount := utl_raw.length(rec.l1); 
   dbms_lob.write(lobloc,utl_raw.length(rec.l1),1,buffer); 
 end loop; 
end; 
/ 

Example #2[LONG/LONG RAW to CLOB]

The following example shows a method for converting LONG columns into CLOB using PL/SQL.

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

Related Posts