oracle blob clob flob操作

--插入bfile­

create or replace procedure insert_book(filename varchar2) as ­

book_file  bfile := NULL;­

bookExists boolean := false;­

begin­

book_file  := bfilename('BOOK_TEXT', filename);­

bookExists  := dbms_lob.fileexists(book_file) = 1;­

­

if bookExists then­

  insert into my_book_files values ((select count(*) from my_book_files) + 1 , book_file);­

  dbms_output.put_line('Insert sucess! file : ' || filename);­

else­

  dbms_output.put_line('Not exists! file : ' || filename);­

   end if;­

exception­

  when dbms_lob.noexist_directory then­

       dbms_output.put_line('Error: ' || sqlerrm);  ­

  when dbms_lob.invalid_directory then­

       dbms_output.put_line('Error : ' || sqlerrm);­

  when others then­

       dbms_output.put_line('Unkown Error : ' || sqlerrm);       ­

end insert_book;­

/­

­

create or replace procedure insertPDF(fileName varchar2) is ­

        fileLoc bfile;­

        nID number;­

        nPDFSize integer;­

        bFileExists boolean := false;­

begin­

     fileLoc := bfilename('PDFDIR',filename);­

     bFileExists := dbms_lob.fileexists(fileLoc) = 1;­

     if bFileExists = false then­

        dbms_output.put_line(fileName || ' not exists');­

        return;­

     end if;­

     ­

     nPDFSize := dbms_lob.getlength(fileLoc);­

     dbms_output.put_line('the length of ' || fileName || ' is ' || nPDFSize);­

     select count(*) + 1 into nID from PDFTable;­

     insert into PDFTable(ID,Pdffile) ­

            values (nID, fileLoc);­

exception ­

  when dbms_lob.noexist_directory then­

       dbms_output.put_line('Error: ' || sqlerrm);  ­

  when dbms_lob.invalid_directory then­

       dbms_output.put_line('Error : ' || sqlerrm);­

  when others then­

       dbms_output.put_line('Unkown Error : ' || sqlerrm);­

end;       ­

/­

­

--插入 blob­

CREATE OR REPLACE procedure insertImg(imgName varchar2) is­

        v_file_loc bfile;­

        v_image blob;­

        nID number;­

        nImgSize integer;­

bFileExists boolean := false;­

begin­

      v_file_loc := bfilename('IMAGEDIR', imgName);­

      bFileExists := dbms_lob.fileExists(v_file_loc) = 1;­

      if bFileExists = false then­

       dbms_output.put_line(imgName || ' not exists');­

       return;­

      end if;­

      nImgSize := dbms_lob.getlength(v_file_loc);­

      dbms_output.put_line(imgName ||' size is ' || nImgSize);­

      dbms_output.put_line('Now Inserting empty image row');­

      select count(*) + 1 into nID from imagetable;­

      insert into imagetable(ID, image)­

             values (nID, empty_blob)­

             returning image into v_image;­

      DBMS_LOB.FILEOPEN (v_file_loc);­

      dbms_output.put_line('Open file');­

      dbms_lob.loadfromfile(v_image, v_file_loc, nImgSize);­

      DBMS_LOB.FILECLOSE(v_file_loc);­

      commit;­

exception­

          when others then­

               dbms_output.put_line('Error happen! ' || sqlerrm);­

        DBMS_LOB.FILECLOSE(v_file_loc);­

end insertImg;­

/­

­

--=================================================­

SQL> create table view_sites_info (­

  2   site_id  number(3),­

  3   audio  blob default empty_blob(),­

  4   document clob default empty_clob(),­

  5   video_file  bfile default null­

  6  );­

表已创建。­

SQL> commit;­

提交完成。­

­

SQL> @e:\writelob­

PL/SQL 过程已成功完成。­

SQL> desc view_sites_info;­

名称                                      是否为空? 类型­

----------------------------------------- -------- ----------------------------­

SITE_ID                                            NUMBER(3)­

AUDIO                                              BLOB­

DOCUMENT                                           CLOB­

VIDEO_FILE                                         BINARY FILE LOB­

SQL> select document from view_sites_info where site_id = 100;­

DOCUMENT                                                                        ­

--------------------------------------------------------------------------------­

This is a writing example                                                       ­

SQL> desc view_sites_info­

名称                                      是否为空? 类型­

----------------------------------------- -------- ----------------------------­

SITE_ID                                            NUMBER(3)­

AUDIO                                              BLOB­

DOCUMENT                                           CLOB­

VIDEO_FILE                                         BINARY FILE LOB­

SQL> insert into blobtest values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'));­

insert into blobtest values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'))­

                                *­

ERROR 位于第 1 行: ­

ORA-00932: 数据类型不一致 ­

­

SQL> desc BFILETEST­

名称                                      是否为空? 类型­

----------------------------------------- -------- ----------------------------­

ID                                                 NUMBER(3)­

FNAME                                              BINARY FILE LOB­

SQL> insert into BFILETEST values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'));­

已创建 1 行。­

­

SQL> get E:\insertimg­

  1  create or replace procedure img_insert (­

  2     tid   varchar2,­

  3     filename  varchar2) as ­

  4     F_LOB   BFILE;­

  5     B_LOB  BLOB;­

  6  begin­

  7   insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;­

  8   F_LOB := bfilename('images', filename);­

  9   dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);­

10   dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));­

11   dbms_lob.fileclose(F_LOB);­

12   commit;­

13* end;­

SQL> r­

  1  create or replace procedure img_insert (­

  2     tid   varchar2,­

  3     filename  varchar2) as ­

  4     F_LOB   BFILE;­

  5     B_LOB  BLOB;­

  6  begin­

  7   insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;­

  8   F_LOB := bfilename('images', filename);­

  9   dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);­

10   dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));­

11   dbms_lob.fileclose(F_LOB);­

12   commit;­

13* end;­