How to write a blob pdf data from the Oracle database to a pdf file

How to write a blob pdf data from the Oracle database to a pdf file

create directory orcl_dir as '/mnt/oracle/orcl_dir'

--In my example I am passing two variables
CREATE OR REPLACE PROCEDURE BLOBToFILESample (typeidin in number,varnamein IN VARCHAR2) IS
type_id number(11);
var_name varchar2(40);
v_blob         BLOB;
  blob_length    INTEGER;
  out_file       UTL_FILE.FILE_TYPE;
  v_buffer       RAW(32767);
  chunk_size     BINARY_INTEGER := 32767;
  blob_position  INTEGER := 1;

BEGIN

  -- Retrieve the BLOB for reading
  SELECT myblobcolumn INTO v_blob FROM mytablename WHERE mycol1=typeidin and mycol2 = varnamein;

  -- Retrieve the SIZE of the BLOB
  blob_length:=DBMS_LOB.GETLENGTH(v_blob);

  --ORCL_DIR is the directory which i have created on my database.
  out_file := UTL_FILE.FOPEN ('ORCL_DIR', varnamein, 'wb', chunk_size);

  -- Write the BLOB to file in chunks
  WHILE blob_position <= blob_length LOOP
    IF blob_position + chunk_size - 1 > blob_length THEN
      chunk_size := blob_length - blob_position + 1;
    END IF;
    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
    blob_position := blob_position + chunk_size;
  END LOOP;

  -- Close the file handle
  UTL_FILE.FCLOSE (out_file);
END;
/

The above code will save the file in the directory orcl_dir.

Some of the error I have encountered is when passing the parameters to the procedure do not give the same name as the column name. If you specify then you may encounter ora-01422 as below

ORA-01422: exact fetch returns more than requested number of rows