Oracle中BLOB/CLOB相关操作的使用笔记

使用pl / sql读取写入blob对象

一、写文件到BLOB
SQL > create table iihero_blob(fid int primary key ,fname varchar ( 32 ),fblob);

表已创建。

SQL > conn / as sysdba
已连接。
SQL > create or replace directoryBLOBDIR as ' d:oraclefile ' ;

目录已创建。

SQL > grant read on directoryBLOBDIR to test;

授权成功。

SQL > conntest / test
已连接。

SQL > create sequences_iihero_seq
2 start with 1
3 increment by 1
4 /

序列已创建。

CREATE OR REPLACE PROCEDURE iihero_load_blob(pfname VARCHAR2 )
IS
src_fileBFILE;
dst_fileBLOB;
lgh_fileBINARY_INTEGER;
BEGIN
src_file: = bfilename( ' BLOBDIR ' ,pfname);

INSERT INTO iihero_blob(fid,fname,f)
VALUES (S_IIHERO_SEQ.Nextval,pfname,EMPTY_BLOB())
RETURNINGf INTO dst_file;

SELECT f INTO dst_file
FROM iihero_blob WHERE fname = pfname FOR UPDATE ;

dbms_lob.fileopen(src_file,dbms_lob.file_readonly);
lgh_file: = dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file,src_file,lgh_file);

UPDATE iihero_blob SET f = dst_file
WHERE fname = pfname;

dbms_lob.fileclose(src_file);
commit ;
END iihero_load_blob;
/


SQL > colsegment_name for a30
SQL > conn / as sysdba
已连接。
SQL > select segment_name,segment_type,bytes / 1024 / 1024 from dba_segments where ow
ner = ' TEST ' ;

SEGMENT_NAMESEGMENT_TYPEBYTES / 1024 / 1024
-- -------------------------------------------------------------
ST_COURSE TABLE . 0625
TAB2 TABLE . 0625
IIHERO_BLOB TABLE . 0625
SYS_LOB0000030667C00003$$LOBSEGMENT. 0625

已选择34行。

SQL > conntest / test
已连接。
SQL >

4 .装数据(写数据到BLOB)

SQL > exec iihero_load_blob( ' ttt.txt ' );

PL / SQL过程已成功完成。

SQL > select fid,fname,dbms_lob.getlength(f) from iihero_blob;

FIDFNAMEDBMS_LOB.GETLENGTH(F)
-- -------------------------------------------------------------
1 ttt.txt 28729

二、将BLOB中的数据读到文件
1 .确认有数据
SQL > select fid,fname,dbms_lob.getlength(f) from iihero_blob;

FIDFNAMEDBMS_LOB.GETLENGTH(F)
-- -------------------------------------------------------------
1 ttt.txt 28729

2 .创建目录
SQL > conn / as sysdba
已连接。
SQL > create or replace directoryBLOBDIR as ' d:oraclefile ' ;
目录已创建。
SQL > grant read ,write on directoryBLOBDIR to test;
授权成功。

3 .创建存储过程
conntest / test

CREATE OR REPLACE PROCEDURE iihero_dump_blob(piname varchar2 ,poname varchar2 ) IS
l_fileUTL_FILE.FILE_TYPE;
l_buffer RAW ( 32767 );
l_amountBINARY_INTEGER: = 32767 ;
l_pos INTEGER : = 1 ;
l_blobBLOB;
l_blob_len INTEGER ;
BEGIN
SELECT f INTO l_blob FROM iihero_blob WHERE FNAME = piname;
l_blob_len: = DBMS_LOB.GETLENGTH(l_blob);
-- dbms_output.put_line(l_blob_len);
-- l_file:=UTL_FILE.FOPEN('BLOBDIR',poname,'wb',32767);
l_file: = UTL_FILE.FOPEN( ' BLOBDIR ' ,poname, ' w ' , 32767 );

WHILE l_pos < l_blob_lenLOOP
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 ;
/

SQL > hostdir / b / sd:oraclefile
d:oraclefileexample.txt
d:oraclefile tt.txt

<script type="text/javascript"><!-- google_ad_client = "ca-pub-7104628658411459"; /* wide1 */ google_ad_slot = "8564482570"; google_ad_width = 728; google_ad_height = 90; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>