教程(视频,书籍)下载:  ASP.NET AutoCAD 数据库 C# ASP java photoshop 网页设计 delphi 3dmax Flash C++ VB 张孝祥 实例   更多请进入BIBIDU搜索

--插入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 (

来源:upschool.com.cn
作者:
关键字：Oracle进阶,LOB字段学习
发表日期：2007-1-6 0:32:47

上一篇：从MySQL中导出XLS数据库工具   下一篇：PLSQL单行函数和组函数详解

共6页 9 7 [1] [2] [3] [4] [5] [6] 8 :>

2008-12-3 6:58:06

本文的相类似文章

Oracle进阶 LOB字段学习