How to lob

See Tablespaces to move lob's tablespace

CREATE TABLE TECH_ORACLE_TEXT
(
  COGNOME      VARCHAR2(50 BYTE),
  NOME         VARCHAR2(50 BYTE),
  EMAIL        VARCHAR2(50 BYTE),
  VARCHAR4000  VARCHAR2(4000 BYTE),
  DESCR_CLOB   CLOB,
  DESCR_BLOB   BLOB
);

create directory D_LOAD_BLOB as 'c:\temp';

Copy a file to a blob field

declare
	   lobd BLOB;
	   directoryName varchar2(50) := 'D_LOAD_BLOB';
	   fileName varchar2(50) := 'me.jpg';
	   fils BFILE;
BEGIN
	 fils := BFILENAME(directoryName, fileName);
	 --Create a temporary blob, not in cache only for call duration 
	 dbms_lob.createtemporary(lobd, false, 2);
	 dbms_lob.open(fils, dbms_lob.lob_readonly);
	 dbms_lob.loadfromfile(lobd, fils, DBMS_LOB.GETLENGTH(fils));
	 dbms_lob.close(fils);
	 INSERT INTO ALDO.TECH_ORACLE_TEXT (COGNOME, NOME, EMAIL, VARCHAR4000, DESCR_CLOB, DESCR_BLOB, blob_type) 
	 VALUES ( 'fadalti', 'aldo', 'aldo@fadalti.com', 'uno due tre quattro', 'cinque sei sette', lobd, fileName);
end;

Write a blob to file

I don't like the external procedure way. This is much better.

My Java Utilities

Test the procedure

declare
b blob;
begin
	 select immagine into b from aldo.vsl_imm_immagini_fabbricati
	 where rownum = 1;
	 myjavautils.writeBlobToFile('c:\temp\cula.jpg', b, 0);
end;