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