Oracle Blob Clob Bfilename使用

1.建立一个目录别名

create DIRECTORY 'tmpdir' AS '/tmp';
GRANT READ ON DIRECTORY bfile_dir1 TO scott;

2.
建立一个含有bfile字段的表

create table bfiletest(id number(3), fname bfile);

建立一个含有BLOB字段的表

create table blobtest(id number(3),ablob blob);

3.插入数据
INSERT INTO bfiletest VALUES (1, BFILENAME ('XMLDIR', 'tmptest'));
4.bfile转blob

CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc(
TID IN NUMBER,rfilename in varchar2,upmessage out varchar2)
AS
Dest_loc BLOB;
Src_loc BFILE;
BEGIN
INSERT INTO BLOBTEST(ID,ABLOB) VALUES(TID,EMPTY_BLOB()) RETURN ABLOB INTO DEST_LOC;
Src_loc := BFILENAME('XMLDIR',rfilename);
IF (DBMS_LOB.FILEEXISTS(Src_loc) != 0)
THEN
DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc,DBMS_LOB.GETLENGTH(Src_loc));
DBMS_LOB.CLOSE(Dest_loc);
DBMS_LOB.CLOSE(Src_loc);
COMMIT;
upmessage := '0';
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
upmessage := '操作失败';
END;

5.bfile转clob

create or replace function getDocument(filename varchar2)
return CLOB deterministic
is
file bfile := bfilename('XMLDIR',filename);
charContent CLOB := ' ';
targetFile bfile;
warning number;
begin
targetFile := file;
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromFile(charContent,targetFile, DBMS_LOB.getLength(targetFile),1,1);
DBMS_LOB.fileclose(targetFile);
return charContent;
end;
/

6.Select到LOB字段的定位器
对某一LOB字段进行选择，则返回的不是LOB的值，而是该LOB字段的定位器

DELCARE
AUDIO_INFO BLOB；
BENGIN
SELECT ablob INTO AUDIO_INFO FROM blobtest WHERE id=100;
END;