使用PL SQL从数据库中读取BLOB对象.pdf

《使用PL SQL从数据库中读取BLOB对象.pdf》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、使用PL/SQL从数据库中读取BLOB对象1.确认现有对象SQL>colfdescfora30SQL>selectfid,fname,fdescfromeygle_blob;FIDFNAMEFDESC------------------------------------------------------------------------------------------1ShaoLin.jpg少林寺-康熙手书2DaoYing.jpg倒映2.创建存储DirectorySQL>connect/assysdbaConnected.SQL>createorreplac

2、edirectoryBLOBDIRas'D:oradataPic';Directorycreated.SQL>SQL>grantread,writeondirectoryBLOBDIRtoeygle;Grantsucceeded.SQL>3.创建存储过程SQL>connecteygle/eygleConnected.SQL>SQL>CREATEORREPLACEPROCEDUREeygle_dump_blob(pinamevarchar2,ponamevarchar2)IS2l_fileUTL_FILE.FILE_TYPE;3l_bufferRAW(32767);

3、4l_amountBINARY_INTEGER:=32767;5l_posINTEGER:=1;6l_blobBLOB;7l_blob_lenINTEGER;8BEGIN9SELECTFPIC10INTOl_blob11FROMeygle_blob12WHEREFNAME=piname;1314l_blob_len:=DBMS_LOB.GETLENGTH(l_blob);15l_file:=UTL_FILE.FOPEN('BLOBDIR',poname,'wb',32767);1617WHILEl_pos

4、_blob,l_amount,l_pos,l_buffer);19UTL_FILE.PUT_RAW(l_file,l_buffer,TRUE);20l_pos:=l_pos+l_amount;21ENDLOOP;2223UTL_FILE.FCLOSE(l_file);2425EXCEPTION26WHENOTHERSTHEN27IFUTL_FILE.IS_OPEN(l_file)THEN28UTL_FILE.FCLOSE(l_file);29ENDIF;30RAISE;31END;32/Procedurecreated.4.取出数据SQL>hostls-ld:ora

5、dataPictotal7618-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpgSQL>execeygle_dump_blob('ShaoLin.jpg','01.jpg')PL/SQLproceduresuccessfullycompleted.SQL>hostls-ld:oradataPictotal11072-rwxrwxrwa1AdministratorsSYSTEM1768198Apr2607:1601.jpg

6、-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpgSQL>SQL>execeygle_dump_blob('DaoYing.jpg','02.jpg')PL/SQLproceduresuccessfullycompleted.SQL>hostls-ld:oradataPictotal15236-rwxrwxrwa1AdministratorsSYSTEM1768198Apr2607:1601.jpg-rwxrwxrwa1Ad

7、ministratorsSYSTEM2131553Apr2607:1902.jpg-rwxrwxrwa1gqgaiNone2131553Apr1910:12DaoYing.jpg-rwxrwxrwa1gqgaiNone1768198Apr1910:12ShaoLin.jpg