How to View PDF file saved as BLOB datatype in Oracle DB

Question

I am trying to view the PDF file saved as BLOB data on Oracle DB.

Below is the function that I could progress so far with the help of Google.

    create or replace FUNCTION CONCAT_BLOB_EXT_DOC (MRECID NUMBER)
    RETURN XMLTYPE
    IS
      v_clob   CLOB;
      v_xml    XMLTYPE;
      p_pdf   BLOB;
    BEGIN 
      select blob_filed into p_pdf from table_name where id = MRECID;
      DBMS_LOB.createtemporary(v_clob, TRUE);
      DBMS_LOB.open(v_clob, DBMS_LOB.lob_readwrite);
      ctx_doc.ifilter(p_pdf, v_clob);
      DBMS_LOB.close(v_clob);
      v_xml := xmltype(REGEXP_REPLACE(v_clob, ']+>'));
      DBMS_LOB.freetemporary(v_clob);
    RETURN v_xml;
    END;

Can someone help me to sort out where I am going wrong.

Answer 1

It depends on where and how you want to see your pdf file saved as BLOB in your database:

1. using SQL Developer

• Configure an appropriate external editor to view PDFs (Preferences -> External Editor -> application/pdf)
• Query the row(s), and view them in the editor (the pencil icon in the result set) and click External Editor
  More details at : https://www.thatjeffsmith.com/archive/2013/10/sql-developer-blobs-and-the-external-editor/

2. within APEX region

• read this: https://www.javainhand.com/2020/04/preview-pdf-image-into-a-region-in-oracle-apex.html

3. saving it as a file

• you need a Directory Object in db
• you need acces to the Directory Object's folder to open the file
  Below is the code to save a blob to a file:

Function BLOB2FILE (mBLOB BLOB, mDir VARCHAR2, mFile VARCHAR2) RETURN VarChar2
IS
BEGIN
  Declare
    utlFile       UTL_FILE.FILE_TYPE;
    utlBuffer     RAW(32767);
    utlAmount     BINARY_INTEGER := 32767;
    utlPos        INTEGER := 1;
    utlBlobLen    INTEGER;
    mRet          VarChar2(512);
  Begin
    utlBlobLen := DBMS_LOB.GetLength(mBLOB);
    utlFile := UTL_FILE.FOPEN(mDir, mFile,'wb', 32767);
--
    WHILE utlPos <= utlBlobLen LOOP
      DBMS_LOB.READ(mBLOB, utlAmount, utlPos, utlBuffer);
      UTL_FILE.PUT_RAW(utlFile, utlBuffer, TRUE);
      utlPos := utlPos + utlAmount;
    END LOOP;
--
    UTL_FILE.FCLOSE(utlFile);
    mRet := 'OK - file created ' || mFile;
    RETURN mRet;
  Exception
    WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN(utlFile) THEN
        UTL_FILE.FCLOSE(utlFile);
      END IF;
      mRet := 'ERR - BLOB2FILE error message  ' || Chr(10) || SQLERRM;
      RETURN mRet;
  End;
END BLOB2FILE;

Doing it:

SET SERVEROUTPUT ON
Declare
    p_blob      BLOB;
    p_status    VarChar2(512);
Begin
    Select YOUR_BLOB_COLUMN Into p_blob From YOUR_TABLE Where PK_COLUMN = 1;
    p_status := BLOB2FILE(p_blob, 'your_directory_object', 'f_name.pdf');
    dbms_output.put_line(p_status);
End;
--
--  OK - file created f_name.pdf