{"id":27601,"date":"2025-04-26T04:38:59","date_gmt":"2025-04-26T04:38:59","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=27601"},"modified":"2026-03-04T05:18:18","modified_gmt":"2026-03-04T05:18:18","slug":"store-images-in-oracle-blob","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/store-images-in-oracle-blob.html","title":{"rendered":"Store images in Oracle BLOB"},"content":{"rendered":"<h2>Oracle BLOB Type Features<\/h2>\n<ul>\n<li>Oracle-native large object type for binary data storage<\/li>\n<li>Supports image sizes up to 4GB per BLOB (Oracle 11g+)<\/li>\n<li>Distinct from BFILE (stores pointer to external files, not binary data)<\/li>\n<li>Integrated with Oracle&#8217;s Advanced LOB Compression and Encryption<\/li>\n<\/ul>\n<h2>Step 1: Create a Table with BLOB Column<\/h2>\n<pre>CREATE TABLE image_storage (\r\n    image_id NUMBER PRIMARY KEY,\r\n    image_name VARCHAR2(100),\r\n    image_blob BLOB,\r\n    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP\r\n) TABLESPACE users; -- Specify dedicated tablespace for LOB data\r\n<\/pre>\n<p><strong>Optional:<\/strong> Create separate LOB segment for better performance<\/p>\n<pre>CREATE TABLE image_storage (\r\n    image_id NUMBER PRIMARY KEY,\r\n    image_name VARCHAR2(100),\r\n    image_blob BLOB,\r\n    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP\r\n) \r\nLOB (image_blob) STORE AS SECUREFILE (\r\n    TABLESPACE lob_ts -- Dedicated tablespace for BLOB data\r\n    ENABLE STORAGE IN ROW\r\n    CACHE READS\r\n);\r\n<\/pre>\n<h2>Step 2: Insert Images into Oracle BLOB<\/h2>\n<h3>Method 1: Using PL\/SQL (Oracle SQL Developer\/Command Line)<\/h3>\n<pre>DECLARE\r\n    v_blob BLOB;\r\n    v_file BFILE := BFILENAME('IMAGE_DIR', 'product_photo.jpg'); -- IMAGE_DIR = Oracle directory object\r\nBEGIN\r\n    -- Initialize empty BLOB\r\n    INSERT INTO image_storage (image_id, image_name, image_blob)\r\n    VALUES (1, 'product_photo.jpg', EMPTY_BLOB())\r\n    RETURNING image_blob INTO v_blob;\r\n\r\n    -- Open and load file into BLOB\r\n    DBMS_LOB.OPEN(v_file, DBMS_LOB.LOB_READONLY);\r\n    DBMS_LOB.LOADFROMFILE(v_blob, v_file, DBMS_LOB.GETLENGTH(v_file));\r\n    DBMS_LOB.CLOSE(v_file);\r\n    \r\n    COMMIT;\r\nEND;\r\n\/\r\n<\/pre>\n<p><strong>Prerequisite:<\/strong> Create Oracle directory object first<\/p>\n<pre>CREATE DIRECTORY IMAGE_DIR AS '\/path\/to\/your\/image\/folder';\r\nGRANT READ, WRITE ON DIRECTORY IMAGE_DIR TO your_username;\r\n<\/pre>\n<h3>Method 2: Using DBBlobEditor<\/h3>\n<h4>Insert Single Image into Oracle BLOB<\/h4>\n<ol>\n<li>Connect to your Oracle database in <a href=\"\/dbblobeditor\/\">DBBlobEditor<\/a><\/li>\n<li>Navigate to the &#8220;image_storage&#8221; table in the target schema<\/li>\n<li>Click \u201c+\u201d button to add a record, and then <a href=\"\/blog\/oralobeditor\/view-image-data-in-oracle-blob.html\">Edit image stored in Oracle BLOB field<\/a><\/li>\n<\/ol>\n<h4>Batch Insert Images into Oracle BLOB<\/h4>\n<ul>\n<li><a href=\"\/blog\/oralobeditor\/import-images-to-oracle-blob.html\">Batch import image files into Oracle BLOB<\/a><\/li>\n<\/ul>\n<h2>Step 3: Retrieve and Restore Oracle BLOB to Image<\/h2>\n<h3>Method 1: PL\/SQL to Export BLOB to File<\/h3>\n<pre>DECLARE\r\n    v_blob BLOB;\r\n    v_file BFILE := BFILENAME('EXPORT_DIR', 'exported_photo.jpg');\r\n    v_output 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    -- Get BLOB data from table\r\n    SELECT image_blob INTO v_blob FROM image_storage WHERE image_id = 1;\r\n\r\n    -- Export BLOB to file\r\n    v_output := UTL_FILE.FOPEN('EXPORT_DIR', 'exported_photo.jpg', 'wb', 32767);\r\n    WHILE v_pos &lt;= DBMS_LOB.GETLENGTH(v_blob) LOOP\r\n        DBMS_LOB.READ(v_blob, v_amount, v_pos, v_buffer);\r\n        UTL_FILE.PUT_RAW(v_output, v_buffer, TRUE);\r\n        v_pos := v_pos + v_amount;\r\n    END LOOP;\r\n    UTL_FILE.FCLOSE(v_output);\r\n    \r\n    COMMIT;\r\nEND;\r\n\/\r\n<\/pre>\n<h3>Method 2: DBBlobEditor Export<\/h3>\n<h4>Retrieve and Restore Single Image<\/h4>\n<ol>\n<li>Connect to Oracle database in DBBlobEditor<\/li>\n<li>Query the &#8220;image_storage&#8221; table and locate the target image_blob column<\/li>\n<li>Double click to <a href=\"\/blog\/oralobeditor\/view-image-data-in-oracle-blob.html\">View image stored in Oracle BLOB field<\/a><\/li>\n<li>Click menu \u201cFile\u201d -&gt; \u201cSave\u201d to save BLOB data as a local image file<\/li>\n<\/ol>\n<h4>Batch Export Oracle BLOB to Images<\/h4>\n<ul>\n<li><a href=\"\/blog\/oralobeditor\/export-images-from-oracle-to-files.html\">Export images from Oracle to files<\/a><\/li>\n<\/ul>\n<h2>Oracle Specific Notes<\/h2>\n<ul>\n<li>Use <strong>SECUREFILE LOBs<\/strong> (Oracle 11g+) for better performance with large images (replace BASICFILE)<\/li>\n<li>Assign dedicated LOB tablespaces to avoid performance impact on regular tables<\/li>\n<li>Grant necessary privileges: CREATE DIRECTORY, UTL_FILE, DBMS_LOB<\/li>\n<li>Avoid using BLOB columns in indexes (use image_id or image_name for filtering)<\/li>\n<li>For high concurrency, use LOB caching (CACHE READS) and disable logging temporarily<\/li>\n<\/ul>\n<hr \/>\n<h3>Related Guides<\/h3>\n<ul>\n<li><a href=\"\/blog\/blob\/images-in-blob.html\">Store images in BLOB: A Comprehensive Guide<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-db2-blob.html\">Store images in DB2 BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sql-server-varbinary.html\">Store images in SQL Server VARBINARY<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-mysql-blob.html\">Store images in MySQL BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-postgresql-bytea.html\">Store images in PostgreSQL BYTEA<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sqlite-blob.html\">Store images in SQLite BLOB<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Oracle BLOB Type Features Oracle-native large object type for binary data storage Supports image sizes up to 4GB per BLOB (Oracle 11g+) Distinct from BFILE (stores pointer to external files, not binary data) Integrated with Oracle&#8217;s Advanced LOB Compression and Encryption Step 1: Create a Table with BLOB Column CREATE TABLE image_storage ( image_id NUMBER [&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,2065,1325,4494],"class_list":["post-27601","post","type-post","status-publish","format-standard","hentry","category-blob","tag-blob","tag-image","tag-oracle","tag-photo"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27601","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=27601"}],"version-history":[{"count":7,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27601\/revisions"}],"predecessor-version":[{"id":45321,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27601\/revisions\/45321"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=27601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=27601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=27601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}