PLSQL/SQL code to convert BLOB data To CLOB


create or replace function clobfromblob(p_blob blob) return clob is
      l_clob         clob;
      l_dest_offsset integer := 1;
      l_src_offsset  integer := 1;
      l_lang_context integer := dbms_lob.default_lang_ctx;
      l_warning      integer;

   begin

      if p_blob is null then
         return null;
      end if;

      dbms_lob.createTemporary(lob_loc => l_clob
                              ,cache   => false);

      dbms_lob.converttoclob(dest_lob     => l_clob
                            ,src_blob     => p_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);

      return l_clob;

   end;
   
 Declare 
  VARIABLECLOB CLOB;
  Temp_Save Varchar2(32767); --//whether it is greater than 4000

  Begin
  
  SELECT clobfromblob(A.JMS_VALUE) Into Temp_Save from AUD_JMS A;  
  Variableclob:=To_Clob(Temp_Save);
  Dbms_Output.Put_Line(Variableclob);

  End;

No comments:

Post a Comment

About OIM

Oracle Identity Management enables organizations to effectively manage the end - to - end life - cycle of user ide...

Popular Posts