conn sys/@oracle_dbname as sysdba create user giga identify by giga default tablespace users; grant connect,resource,dba to giga; create or replace directory blod_dir as 'd:\oralce\pic'; grant read on directory blod_dir to giga;conn giga/giga create table giga_blod( fid number primary key, fname varchar2(20), fpic blob);create or replace procedure(pfid in number,pfname in varchar2) is temp_bfile bfile; inserted_blob_file blob; begin temp_bfile := bfilename('blod_dir',pfname) insert into giga_table(fid,fname,fpic) values (pfid,pfname,empty_blob()) returning fpic into inserted_blob_file; select fpic into inserted_blob_file from giga_table where fname=pfname for update; dbms_lob.fileopen(temp_bfile,dbms_lob.readonly); dbms_lob.loadfromfile(inserted_blob_file,temp_bfile,dbms_lob.getlength(temp_bfile)); update giga_blob set fpic=inserted_blob where fname=pfname; dbms_lob.filecolse(temp_bfile); commit;
非常感谢,但是怎样从表中读取呢?我有一个例子读取 txt 文件没有问题,但是读取 jpg 和 doc文件就有问题。oracle 9i下。麻烦帮我看看create or replace procedure leslie_dump_blob(piname in varchar2, poname in varchar2) is l_file Utl_File.file_type; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob BLOB; l_blob_len INTEGER; begin SELECT FPIC INTO l_blob FROM leslie_bloc WHERE FNAME = piname;
create user giga identify by giga default tablespace users;
grant connect,resource,dba to giga;
create or replace directory blod_dir as 'd:\oralce\pic';
grant read on directory blod_dir to giga;conn giga/giga
create table giga_blod(
fid number primary key,
fname varchar2(20),
fpic blob);create or replace procedure(pfid in number,pfname in varchar2)
is
temp_bfile bfile;
inserted_blob_file blob;
begin
temp_bfile := bfilename('blod_dir',pfname)
insert into giga_table(fid,fname,fpic)
values (pfid,pfname,empty_blob())
returning fpic into inserted_blob_file;
select fpic into inserted_blob_file
from giga_table
where fname=pfname for update;
dbms_lob.fileopen(temp_bfile,dbms_lob.readonly);
dbms_lob.loadfromfile(inserted_blob_file,temp_bfile,dbms_lob.getlength(temp_bfile));
update giga_blob set fpic=inserted_blob
where fname=pfname;
dbms_lob.filecolse(temp_bfile);
commit;
is
l_file Utl_File.file_type;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
begin SELECT FPIC
INTO l_blob
FROM leslie_bloc
WHERE FNAME = piname;
l_blob_len := DBMS_LOB.getlength(l_blob);
l_file := UTL_FILE.fopen('UTL_FILE_DIR',poname,'w',32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob,l_amount,l_pos,l_buffer);
UTL_FILE.put_raw(l_file,l_buffer,TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose(l_file);
EXCEPTION
when others then
IF Utl_File.is_open(l_file) then
utl_file.fclose(l_file);
END IF;
RAISE;
end leslie_dump_blob;
1、美工 1名 软件界面设计
2、网站制作 1名 HTML + JavaScript(PHP)
3、程序员 2名 Win32 API + C\C++
4、数据库管理员 1名 Oracle; SQL Sever 2000