Load image in base64 formate in database column from directory reference filename stored in table

11.)    First create base64encode function

 CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB)

  RETURN CLOB

-- -----------------------------------------------------------------------------------

-- File Name    : https://oracle-base.com/dba/miscellaneous/base64encode.sql

-- Author       : Tim Hall

-- Description  : Encodes a BLOB into a Base64 CLOB.

-- Last Modified: 09/11/2011

-- -----------------------------------------------------------------------------------

IS

  l_clob CLOB;

  l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573

BEGIN

  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP

    l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1)));

  END LOOP;

  RETURN l_clob;

END;

/

2.) Then create code

declare

cursor r_cur is

       select filename from folder_image;

       dest_loc  BLOB;

       dest1_loc  BLOB;

       base_64 clob;

       src_loc   BFILE;

       l_filename varchar2(200);

BEGIN

    for l_file in r_cur loop

    src_loc := BFILENAME('EXAMPLE_LOB_DIR', l_file.filename);

   

    INSERT INTO test_blob (id, file_name, image, timestamp)

        VALUES (1002, l_file.filename, empty_blob(), sysdate)

        RETURNING image INTO dest_loc;

    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

    DBMS_LOB.LOADFROMFILE(

          dest_lob => dest_loc

        , src_lob  => src_loc

        , amount   => DBMS_LOB.getLength(src_loc));

    DBMS_LOB.CLOSE(dest_loc);

    DBMS_LOB.CLOSE(src_loc);

    l_filename:= l_file.filename;

         select image into dest1_loc from test_blob where file_name= l_filename;

         base_64:= base64encode(dest1_loc);

         update test_blob set IMAGE_BASE64=base_64 where file_name= l_filename;

    end loop;

    COMMIT;

END;

/