Load file from server directory to oracle table CLOB column by dbms_lob.loadfromfile

--Load file from server directory to oracle table CLOB column by dbms_lob.loadfromfile

CREATE OR REPLACE PROCEDURE XXEAA_LOAD_A_FILE_P
   ( p_dir_name in  VARCHAR2,
     p_file_name  in VARCHAR2
   ) IS
  l_bfile   BFILE;
  l_clob    CLOB;
BEGIN
   l_bfile := BFILENAME(p_dir_name, p_file_name);
   IF (dbms_lob.fileexists(l_bfile) = 1) THEN
      dbms_output.put_line('File Exists');
      INSERT INTO XXERP.XXEAA_QNB_HOST2HOST_CHECKS T
      (cp_request_id,FILE_TEXT,FILE_NAME)
        VALUES (xxpk_debug_Seq.nextval,               
               EMPTY_CLOB(),
               p_file_name
      ) RETURN FILE_TEXT INTO l_clob;
      L_BFILE := bfilename(p_dir_name, p_file_name);
      dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
      dbms_lob.fileclose( l_bfile );
      COMMIT;
   ELSE
     dbms_output.put_line('File does not exist');
   END IF;
   exception when others then
   xxpk_debug_prc('XXEAA_LOAD_A_FILE_P','Main Exception','ZZ',null,substr(SQLERRM,1,299)); 
END;