load_file.sql

load_file.sql

/* 
Load Files into Clob from Filesystem Using PL/SQL
Dee Clawson
20210814
MIT License
*/

res                 CLOB;
v_bfile             BFILE;
v_temp_blob         BLOB;
lob_len             INTEGER;
l_width             PLS_INTEGER := 12000;
pos                 PLS_INTEGER;
v_buffer            VARCHAR2 (32767);

EXECUTE IMMEDIATE 'create or replace directory EXTERNAL_DOCS as ''' || file_path || '''';

v_bfile := BFILENAME('EXTERNAL_DOCS', v.file_name);

IF (dbms_lob.fileexists(v_bfile) = 1) THEN

    DBMS_LOB.CREATETEMPORARY (v_temp_blob, TRUE);

    DBMS_LOB.fileopen (v_bfile);

    DBMS_LOB.loadfromfile (
        dest_lob  => v_temp_blob,
        src_lob    => v_bfile,
        amount    => DBMS_LOB.getlength (v_bfile)
    );
    DBMS_LOB.fileclose (v_bfile);

    pos := 1;
    lob_len := DBMS_LOB.getlength (v_temp_blob);

    DBMS_LOB.createtemporary (res, TRUE);
    DBMS_LOB.open (res, DBMS_LOB.lob_readwrite);

    WHILE (pos < lob_len)
    LOOP
        buffer := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.SUBSTR (v_temp_blob, l_width, pos)));
        DBMS_LOB.writeappend (res, LENGTH (v_buffer), v_buffer);
        pos := pos + l_width;
    END LOOP;

    DBMS_LOB.FREETEMPORARY (v_temp_blob); 

END IF;