Oracle进阶LOB字段学习
添加时间: 2008-4-26 1:12:10 作者: Oracle指导 阅读次数:10 来源: http://www.d9soft.com
--插入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;
/
Oracle进阶LOB字段学习(1) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 在同一台机运行多个Mysql服务(下) 下一篇文章: 理解Oracle10g的SQL正则表达式支持
相关文章: