{"id":40201,"date":"2025-10-15T05:24:18","date_gmt":"2025-10-15T05:24:18","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=40201"},"modified":"2025-10-15T05:24:18","modified_gmt":"2025-10-15T05:24:18","slug":"oracle-export-blob-image","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/oracle-export-blob-image.html","title":{"rendered":"Oracle Export BLOB as Image using PL\/SQL"},"content":{"rendered":"<p>Extract images from Oracle BLOB.<\/p>\n<p>Create an Oracle Directory Object: This object maps a logical name within the database to a physical directory on the database server&#8217;s file system.<\/p>\n<pre>    CREATE OR REPLACE DIRECTORY MY_IMAGES AS '\/path\/to\/your\/export\/directory';\r\n    GRANT READ, WRITE ON DIRECTORY MY_IMAGES TO YOUR_USER;\r\n<\/pre>\n<p>Ensure the specified directory exists on the database server and the Oracle database user has write permissions to it.<\/p>\n<p>Write a PL\/SQL procedure: Read the BLOB data from your table and write it to files.<\/p>\n<pre>    DECLARE\r\n        l_file UTL_FILE.FILE_TYPE;\r\n        l_buffer RAW(32767);\r\n        l_amount BINARY_INTEGER := 32767;\r\n        l_pos NUMBER := 1;\r\n        l_blob BLOB;\r\n        l_blob_len NUMBER;\r\n    BEGIN\r\n        FOR rec IN (SELECT image_id, image_data, image_name FROM your_image_table) LOOP\r\n            l_blob := rec.image_data;\r\n            l_blob_len := DBMS_LOB.GETLENGTH(l_blob);\r\n\r\n            l_file := UTL_FILE.FOPEN('MY_IMAGES', rec.image_name || '.jpg', 'wb', 32767); -- Adjust extension as needed\r\n\r\n            WHILE l_pos &lt; l_blob_len LOOP\r\n                DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);\r\n                UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);\r\n                l_pos := l_pos + l_amount;\r\n            END LOOP;\r\n\r\n            UTL_FILE.FCLOSE(l_file);\r\n            DBMS_OUTPUT.PUT_LINE('Exported ' || rec.image_name || '.jpg');\r\n            l_pos := 1; -- Reset position for next BLOB\r\n        END LOOP;\r\n    EXCEPTION\r\n        WHEN OTHERS THEN\r\n            IF UTL_FILE.IS_OPEN(l_file) THEN\r\n                UTL_FILE.FCLOSE(l_file);\r\n            END IF;\r\n            RAISE;\r\n    END;\r\n    \/\r\n<\/pre>\n<p>And you and use <a href=\"https:\/\/www.withdata.com\/dbblobeditor\/\">DBBlobEditor<\/a> to <a href=\"https:\/\/www.withdata.com\/blog\/oralobeditor\/export-images-from-oracle-to-files.html\">Export images from Oracle to files<\/a>.<\/p>\n<p>See also: <a href=\"https:\/\/www.withdata.com\/blog\/blob\/oracle-blob.html\">Oracle BLOB<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Extract images from Oracle BLOB. Create an Oracle Directory Object: This object maps a logical name within the database to a physical directory on the database server&#8217;s file system. CREATE OR REPLACE DIRECTORY MY_IMAGES AS &#8216;\/path\/to\/your\/export\/directory&#8217;; GRANT READ, WRITE ON DIRECTORY MY_IMAGES TO YOUR_USER; Ensure the specified directory exists on the database server and the [&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],"class_list":["post-40201","post","type-post","status-publish","format-standard","hentry","category-blob","tag-blob","tag-oracle"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/40201","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=40201"}],"version-history":[{"count":1,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/40201\/revisions"}],"predecessor-version":[{"id":40202,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/40201\/revisions\/40202"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=40201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=40201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=40201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}