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 file if something goes wrong.
  IF utl_file.is_open(l_file) THEN
    utl_file.fclose(l_file);
  END IF;
  raise;
END blob_to_file;

Step 2: Call the procedure and test it.

  • Store BLOB in database
  • Push BLOB files into directory


CREATE TABLE store_blob_clob_files
  (
    id NUMBER,
    blob_content BLOB,
    clob_content CLOB
  );

DECLARE
  l_blob BLOB;
BEGIN
  -- Get BLOB file

  SELECT blob_content
    INTO l_blob
    FROM store_blob_clob_files
   WHERE ID = 1;

  -- Push BLOB files into directory

  blob_to_file (p_blob => l_blob,
                        p_dir  => 'TEMP',
                        p_filename => 'samplereport.pdf'
                       );

END;

Step 3: Check the generated file exists in the directory.

Related Posts:

Comments

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Querying Data From Flat Files in Oracle

Number of Tables by The Number of Rows in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Remove X (Close Mark) from Oracle APEX Modal Dialog

Display User Rating Icons Dynamically in Oracle APEX Report

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Find Where Specific Table or View is Used in Oracle Database