PL/SQL Write BLOB to file

PL/SQL Write BLOB to file (temp file):

in my case I have a table with BLOB column, I keep my pdf files inside, and I need to save them in temp directory. This procedure accepts 3 arguments: BLOB, directory name to save into and file name:
CREATE OR REPLACE PROCEDURE write_pdf(v_blob    IN BLOB,
                                        p_directory   IN VARCHAR2,
                                        p_filename    IN VARCHAR2
                                       )
IS
    v_length      INTEGER;
    v_index       INTEGER := 1;
    v_bytecount   INTEGER;
    v_tempraw     RAW(32767);

    v_file        UTL_FILE.file_type;
BEGIN
    v_file  := UTL_FILE.fopen(p_directory, p_filename, 'wb', 32767);

    v_length := DBMS_LOB.getlength(v_blob);
    WHILE v_index <= v_length 
    LOOP
        v_bytecount  := 32767;
        DBMS_LOB.read(v_blob, v_bytecount, v_index, v_tempraw);               

        UTL_FILE.put_raw(v_file, v_tempraw);
        UTL_FILE.fflush(v_file);       

        v_index       := v_index + v_bytecount;
    END LOOP;

    UTL_FILE.fclose(v_file);
END;

Comments

Popular posts from this blog

Certbot

Oracle, how to pass a '&' in url and other special characters