如何使用PL/SQL读取数据库中的BLOB对象

SQL> col fdesc for a30SQL> select fid,fname,fdesc from eygle_blob;    FID FNAME                        FDESC------------------------ ------------------------------   1 ShaoLin.jpg                少林寺-康熙手书   2 DaoYing.jpg                     倒映

SQL> connect / as sysdbaConnected.SQL> create or replace directory BLOBDIR as 'D:oradataPic';Directory created.SQL>SQL> grant read,write on directory BLOBDIR to eygle;Grant succeeded.SQL>

SQL> connect eygle/eygleConnected.SQL>SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varchar2,poname varchar2) IS  2    l_file      UTL_FILE.FILE_TYPE;  3    l_buffer    RAW(32767);  4    l_amount    BINARY_INTEGER := 32767;  5    l_pos       INTEGER := 1;  6    l_blob      BLOB;  7    l_blob_len  INTEGER;  8  BEGIN  9    SELECT FPIC 10    INTO      l_blob 11    FROM      eygle_blob 12    WHERE  FNAME = piname; 13 14    l_blob_len := DBMS_LOB.GETLENGTH(l_blob); 15    l_file := UTL_FILE.FOPEN('BLOBDIR',poname,'wb', 32767); 16 17    WHILE l_pos < l_blob_len LOOP 18      DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer); 19      UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE); 20      l_pos := l_pos + l_amount; 21    END LOOP; 22 23    UTL_FILE.FCLOSE(l_file); 24 25  EXCEPTION 26    WHEN OTHERS THEN 27      IF UTL_FILE.IS_OPEN(l_file) THEN 28        UTL_FILE.FCLOSE(l_file); 29      END IF; 30      RAISE; 31  END; 32  /Procedure created.

SQL> host ls -l d:oradataPictotal 7618-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpgSQL> exec eygle_dump_blob('ShaoLin.jpg','01.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:oradataPictotal 11072-rwxrwxrwa   1 Administrators  SYSTEM          1768198 Apr 26 07:16 01.jpg-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpgSQL>SQL> exec eygle_dump_blob('DaoYing.jpg','02.jpg')PL/SQL procedure successfully completed.SQL> host ls -l d:oradataPictotal 15236-rwxrwxrwa   1 Administrators  SYSTEM          1768198 Apr 26 07:16 01.jpg-rwxrwxrwa   1 Administrators  SYSTEM          2131553 Apr 26 07:19 02.jpg-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa