Posts

Showing posts from May, 2017

Move BLOB files into Oracle Directory

Solution: Step 1: Click  Oracle Directories  to understand about directory creation. Step 2:  Create plsql procdure  blob_to_file . It move BLOB files into respected database directories . CREATE OR REPLACE PROCEDURE blob_to_file (     p_blob     IN OUT nocopy BLOB,     p_dir      IN VARCHAR2,     p_filename IN VARCHAR2) AS   l_file utl_file.file_type;   l_buffer RAW(32767);   l_amount binary_integer := 32767;   l_pos      INTEGER           := 1;   l_blob_len INTEGER; BEGIN   l_blob_len := dbms_lob.getlength(p_blob);   -- Open the destination file.   l_file := utl_file.fopen(p_dir, p_filename,'wb', 32767);   -- Read chunks of the BLOB and write them to the file until complete.   WHILE l_pos <= l_blob_len   LOOP     dbms_lob.READ(p_blob, l_amount, l_pos, l_buffer);     utl_file.put_raw(l_file, l_buffer, TRUE);     l_pos := l_pos + l_amount;   END LOOP;   -- Close the file.   utl_file.fclose(l_file); EXCEPTION WHEN OTHERS THEN   -- Close the