以下的文章主要介绍的是Oracle数据库如何正确用PL/SQL从相关数据库中来准确的读取BLOB字段的实际操作，其中包括如何正确的确认相关对象存在，以及如何创建存储Directory等相关内容介绍。

1.确认对象存在

SQL>colfdescfora30 
SQL>selectfid,fname,fdescfromeygle_blob; 
FIDFNAMEFDESC 

1 ShaoLin.jpg 少林寺-易经经

2 DaoYing.jpg 倒映

2.创建存储Directory

SQL>connect/assysdba 
Connected. 
SQL>createorreplacedirectoryBLOBDIRas'D:\oradata\Pic'; 
Directorycreated. 
SQL>
SQL>grantread,writeondirectoryBLOBDIRtoeygle; 
Grantsucceeded. 
SQL>

3.Oracle数据库用PL/SQL从数据库中读取BLOB字段的过程中我们需要创建存储过程

SQL>connecteygle/eygle 
Connected. 
SQL>
SQL>CREATEORREPLACEPROCEDUREeygle_dump_blob(pinamevarchar2,ponamevarchar2)IS 
l_fileUTL_FILE.FILE_TYPE; 
l_bufferRAW(32767); 
l_amountBINARY_INTEGER:=32767; 
l_posINTEGER:=1; 
l_blobBLOB; 
l_blob_lenINTEGER; 
BEGIN 
SELECTFPIC 
INTOl_blob 
FROMeygle_blob 
WHEREFNAME=piname; 
l_blob_len:=DBMS_LOB.GETLENGTH(l_blob); 
l_file:=UTL_FILE.FOPEN('BLOBDIR',poname,'wb',32767); 
WHILEl_posl_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; 
ENDLOOP; 
UTL_FILE.FCLOSE(l_file); 
EXCEPTION 
WHENOTHERSTHEN 
IFUTL_FILE.IS_OPEN(l_file)THEN 
UTL_FILE.FCLOSE(l_file); 
ENDIF; 
RAISE; 
31END; 
32/ 
Procedurecreated. 

4.取出字段数据

SQL>hostls-ld:\oradata\Pic 
total7618 
-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg 
-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpg 
SQL>execeygle_dump_blob('ShaoLin.jpg','01.jpg') 
PL/SQLproceduresuccessfullycompleted. 
SQL>hostls-ld:\oradata\Pic 
total11072 
-rwxrwxrwa1AdministratorsSYSTEM1768198Apr2607:1601.jpg 
-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg 
-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpg 
SQL>
SQL>execeygle_dump_blob('DaoYing.jpg','02.jpg') 
PL/SQLproceduresuccessfullycompleted. 
SQL>hostls-ld:\oradata\Pic 
total15236 
-rwxrwxrwa1AdministratorsSYSTEM1768198Apr2607:1601.jpg 
-rwxrwxrwa1AdministratorsSYSTEM2131553Apr2607:1902.jpg 
-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg 
-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpg