Oracle LOB 總結

1. BLOB

CREATE TABLE SCOTT.MYBLOB(ID varchar2(50),DATA BLOB);
CREATE OR REPLACE DIRECTORY LOBFILEDIR AS 'F:/LOBDIR';

[a] PL/SQL插入BLOB

DECLARE
   a_blob  BLOB;
   a_bfile BFILE := BFILENAME('LOBFILEDIR','Winter.jpg'); --用來指向外部作業系統
BEGIN  
   INSERT INTO SCOTT.MYBLOB(ID,DATA) VALUES ('20000',EMPTY_BLOB()) returning DATA INTO a_blob;

   dbms_lob.fileopen(a_bfile);
   dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
   dbms_lob.fileclose(a_bfile);
   commit;
end; 

/

[b] SQL插入BLOB

insert into scott.myblob(ID,DATA) values('10000',EMPTY_BLOB());

insert into scott.myblob(ID,DATA) values('30000','101010101101010101101010');

commit;

CLOB是跟BLOB同樣操作.