Subscribe to Green-Card-Lottery's Blog        RSS Feed
-----

PL/SQL: Write Oracle BLOB images to Image files

Icon Leave Comment
If you have a BLOB image table and you want to dump all images to file, this routine will do the trick.
We have a table blob_image_table with the following columns:
image_name varchar2(128)
image_extension varchar2(128)
image_blob BLOB

First create the directory physically on disk, then
as sysdba execute the following commands,
where your_schema is the schema where you will be executing
the procedure from.
create or replace directory datadump as '/home/oracle/datadump';
GRANT read, write ON DIRECTORY datadump TO your_schema; 
GRANT EXECUTE ON UTL_FILE TO your_schema;



CREATE OR REPLACE PROCEDURE unload_images IS
  v_file UTL_FILE.FILE_TYPE;
  l_buffer	RAW(32000);
  l_amount	BINARY_INTEGER := 32000;
  l_pos	   number := 1;
  l_blob	  BLOB;
  l_blob_len  number;
  v_name VARCHAR2(128);

CURSOR Cimage IS select image_name,image_extension,image_blob l_blob,dbms_lob.getlength(image_blob) l_blob_len from blob_image_table;

 RecordsAffected PLS_INTEGER := 0;
 v_CONTENT_LENGTH PLS_INTEGER := 0;
 v_CONTENT_LENGTH2 PLS_INTEGER := 0;
 v_content  clob;
 j PLS_INTEGER := 0;
 k PLS_INTEGER;
 l PLS_INTEGER;
 vn_the_rest PLS_INTEGER :=0;
 vn_the_rest2 PLS_INTEGER :=0;
 v_image_count number;
 x number;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('Begin Time: '||to_char(sysdate,'MM/DD/YYYY hh:mm:ss'));


   FOR Rec IN Cimage LOOP
		x := l_blob_len;
	-- Open the destination file.
		v_name := Rec.image_name||'.'||Rec.image_extension;
		v_file := UTL_FILE.FOPEN('DATADUMP',v_name,'wb',32767);
		if Rec.l_blob_len < 32000 then
			DBMS_LOB.read(Rec.l_blob, Rec.l_blob_len, l_pos, l_buffer);
			utl_file.put_raw(v_file,l_buffer, TRUE);
			utl_file.fflush(v_file);
		else

			WHILE l_pos < Rec.l_blob_len and l_amount > 0 LOOP
				DBMS_LOB.read(Rec.l_blob, l_amount, l_pos, l_buffer);
				UTL_FILE.put_raw(v_file, l_buffer, TRUE);
				utl_file.fflush(v_file);
	-- set the start position for the next cut
				l_pos := l_pos + l_amount;
	-- set the end position if less than 32000 bytes
				x := x - l_amount;
				IF x < 32000 THEN
					l_amount := x;
				END IF;
			END LOOP;
			l_pos := 1;
			l_buffer := null;
			l_blob := null;
			l_amount := 32000;
		end if;
		RecordsAffected := RecordsAffected + 1;
		UTL_FILE.FCLOSE(v_file);
	end if;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('End Time ===> '||to_char(sysdate,'MM/DD/YYYY hh:mm:ss'));
   DBMS_OUTPUT.PUT_LINE('Image Records Affected: '||RecordsAffected);
   EXCEPTION
	WHEN utl_file.invalid_path THEN
		raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
	WHEN utl_file.WRITE_ERROR THEN
		raise_application_error(-20001, 'Operating system error occurred during the write operation.');
	WHEN utl_file.INTERNAL_ERROR THEN
		raise_application_error(-20002, 'Unspecified PL/SQL error.');
	WHEN utl_file.INVALID_OPERATION THEN
		raise_application_error(-20003, 'File could not be opened or operated on as requested.');
	WHEN utl_file.INVALID_FILEHANDLE THEN
		raise_application_error(-20004, 'File handle was invalid.');
	WHEN utl_file.INVALID_MODE THEN
		raise_application_error(-20005, 'The open_mode parameter in FOPEN was invalid.');
	WHEN utl_file.INVALID_MAXLINESIZE THEN
		raise_application_error(-20006, 'Specified max_linesize is too large or too small.');
	WHEN OTHERS THEN
	-- Close the file if something goes wrong.
	IF UTL_FILE.is_open(v_file) THEN
	  UTL_FILE.fclose(v_file);
	END IF;
	RAISE;
END;
/



Then in SQL*PLUS execute the routine
execute unload_images;

0 Comments On This Entry

 

February 2022

S M T W T F S
  12345
67 8 9101112
13141516171819
20212223242526
2728     

Tags

    Recent Entries

    Search My Blog

    2 user(s) viewing

    2 Guests
    0 member(s)
    0 anonymous member(s)