{"id":27608,"date":"2025-04-26T08:23:28","date_gmt":"2025-04-26T08:23:28","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=27608"},"modified":"2026-03-05T05:43:55","modified_gmt":"2026-03-05T05:43:55","slug":"store-pdf-files-in-oracle-blob","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/store-pdf-files-in-oracle-blob.html","title":{"rendered":"Store PDF files in Oracle BLOB"},"content":{"rendered":"<h2>Oracle BLOB Type Overview<\/h2>\n<p>Oracle BLOB (Binary Large Object) stores up to 4 GB of binary data. Differentiate from CLOB (character data) and BFILE (external file pointer).<\/p>\n<h2>Step-by-Step Implementation<\/h2>\n<h3>Method 1: Manual PL\/SQL Statements (Primary Method)<\/h3>\n<h4>1. Create Table<\/h4>\n<pre>CREATE TABLE pdf_storage (\r\n    id NUMBER PRIMARY KEY,\r\n    pdf_name VARCHAR2(255),\r\n    pdf_data BLOB\r\n);<\/pre>\n<h4>2. Insert PDF Data<\/h4>\n<pre>DECLARE\r\n    v_blob BLOB;\r\n    v_file UTL_FILE.FILE_TYPE;\r\n    v_buffer RAW(32767);\r\n    v_amount BINARY_INTEGER := 32767;\r\n    v_pos INTEGER := 1;\r\nBEGIN\r\n    INSERT INTO pdf_storage (id, pdf_name, pdf_data)\r\n    VALUES (1, 'sample.pdf', EMPTY_BLOB())\r\n    RETURNING pdf_data INTO v_blob;\r\n    \r\n    v_file := UTL_FILE.FOPEN('PDF_DIR', 'sample.pdf', 'RB');\r\n    LOOP\r\n        UTL_FILE.READ_RAW(v_file, v_buffer, v_amount);\r\n        DBMS_LOB.WRITE(v_blob, v_amount, v_pos, v_buffer);\r\n        v_pos := v_pos + v_amount;\r\n    END LOOP;\r\nEXCEPTION\r\n    WHEN NO_DATA_FOUND THEN\r\n        UTL_FILE.FCLOSE(v_file);\r\n        COMMIT;\r\nEND;\r\n\/<\/pre>\n<h3>Method 2: Using DBBlobEditor (Optional Visual Tool)<\/h3>\n<p><a href=\"\/dbblobeditor\/\">DBBlobEditor<\/a> supports seamless switching between BFILE and BLOB in Oracle, auto-handles directory permissions, and allows direct preview of PDF data stored in BLOB fields.<\/p>\n<ul>\n<li><a href=\"https:\/\/www.withdata.com\/blog\/oralobeditor\/how-to-view-pdf-stored-in-oracle-blob-field.html\">View PDF stored in Oracle BLOB field<\/a><\/li>\n<li><a href=\"https:\/\/www.withdata.com\/blog\/oralobeditor\/import-pdf-to-oracle-blob.html\">Batch import PDF files into Oracle BLOB<\/a><\/li>\n<li><a href=\"https:\/\/www.withdata.com\/blog\/oralobeditor\/oracle-export-blob-to-pdf.html\">Batch export Oracle BLOB to PDF files<\/a><\/li>\n<\/ul>\n<h2>Oracle BFILE\/BLOB Issues &amp; Solutions<\/h2>\n<ul>\n<li>BFILE vs BLOB: Use BFILE for large external PDFs, BLOB for embedded storage<\/li>\n<li>Tablespace management: Allocate dedicated tablespace for BLOB columns<\/li>\n<\/ul>\n<hr \/>\n<h3>Related Guides<\/h3>\n<ul>\n<li><a href=\"\/blog\/blob\/pdf-in-blob.html\">Store PDF in Database Binary Fields<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-pdf-files-in-db2-blob.html\">Store PDF files in DB2 BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-pdf-files-in-sql-server-varbinary.html\">Store PDF files in SQL Server VARBINARY<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-pdf-files-in-mysql-blob.html\">Store PDF files in MySQL BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-pdf-files-in-postgresql-bytea.html\">Store PDF files in PostgreSQL BYTEA<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-pdf-files-in-sqlite-blob.html\">Store PDF files in SQLite BLOB<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Oracle BLOB Type Overview Oracle BLOB (Binary Large Object) stores up to 4 GB of binary data. Differentiate from CLOB (character data) and BFILE (external file pointer). Step-by-Step Implementation Method 1: Manual PL\/SQL Statements (Primary Method) 1. Create Table CREATE TABLE pdf_storage ( id NUMBER PRIMARY KEY, pdf_name VARCHAR2(255), pdf_data BLOB ); 2. Insert PDF [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4524],"tags":[97,1325,434],"class_list":["post-27608","post","type-post","status-publish","format-standard","hentry","category-blob","tag-blob","tag-oracle","tag-pdf"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27608","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=27608"}],"version-history":[{"count":5,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27608\/revisions"}],"predecessor-version":[{"id":45252,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27608\/revisions\/45252"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=27608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=27608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=27608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}