Oracle: convert BLOB to VARCHAR datatype Oracle Database Tips by Donald BurlesonSeptember 28, 2015

 

Question:  How can I convert a BLOB to a varchar within Oracle?  I tried to fetch only 4000 chunks from the BLOB field to put into a VARCHAR2 field, but, it is throwing the same error - ORA:06502 PL/SQL: numeric or value error: raw variable length too long.

Here is the query I executed for the blob datatype conversion:

SELECT DISTINCT
TQ.TEST_CODE,
TQ.TEST_SORT,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(TEST_ALLOWEDB, 4000,1)) TEST_ALLOWEDB
FROM
TEST_QUESTION TQ
WHERE
INSTR(',1091,1092,1107,1769,1770,1771,1772,1773,1774,1775,',','||TQ.TEST_CODE||',')>0

Please suggest more ideas to handle this code to convert the Oracle BLOB to a character field since RAW Oracle data types are limited to 2000 bytes. 

 

Answer:  The following program selects CLOB data and puts in VARCHAR2 variable:
 

SQL> @test.sql

CREATE OR REPLACE PROCEDURE TEST_PROC AS

CNT NUMBER := 1;
LOCATOR CLOB;
BEGINNING INTEGER :=4000;
ENDING INTEGER := 1;
CLOB_TEXT VARCHAR2(4000);
BEGIN

cursor c is
SELECT TAG
INTO LOCATOR
FROM TEST2
order by TAG;
open c;
LOOP
c = DBMS_LOB.READ (CLOB_TEXT);
DBMS_OUTPUT.PUT_LINE('TAG RECORD = '||c);
END LOOP;
close c;

END;
/

ALTER PROCEDURE TEST_PROC COMPILE;
SHOW ERRORS;

SET SERVEROUTPUT ON SIZE 1000000
EXECUTE TEST_PROC;
 

Laurent Schneider (author of Advanced Oracle SQL Programming) describes the code to convert a BLOB column to a VARCHAR2 column:

 

create or replace function F(B BLOB)
return clob is
  c clob;
  n number;
begin
  if (b is null) then
    return null;
  end if;
  if (length(b)=0) then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  return c;
end;
/