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

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 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;

1 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/

1. within APEX region

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

1. 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  

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