[ORACLE] LONG RAW -> BLOB -> CLOB

-- long raw -> blob

create table blob_table_name
as
select
         to_lob(long_raw_col) as blob_col
  from long_raw_table_name
/

-- create blob2clob function

CREATE OR REPLACE FUNCTION "BLOB2CLOB" (b IN BLOB)
RETURN CLOB
IS
l_blob blob;
l_clob clob := 'x';
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
BEGIN
  l_blob := b;
  IF dbms_lob.getlength(b) != 0 THEN
  DBMS_LOB.CONVERTTOCLOB
  ( dest_lob => l_clob
  , src_blob => l_blob
  , amount => dbms_lob.lobmaxsize
  , dest_offset => l_dest_offsset
  , src_offset => l_src_offsset
  , blob_csid => dbms_lob.default_csid
  , lang_context => l_lang_context
  , warning => l_warning
  );
  END IF;
RETURN l_clob;
END BLOB2CLOB;
/

-- blob --> clob

create table clob_table_name
as
select
          blob2clob(blob_col) as clob_col
  from blob_table_name
/