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

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


1.确认现有对象
SQL> col fdesc for a30
SQL> select fid,fname,fdesc from bobo_blob;

       FID FNAME                                              FDESC
---------- -------------------------------------------------- ------------------------------
         1 ShaoLin.jpg                                        少林寺-康熙手书
         2 DaoYing.jpg                                        倒映


2.创建存储Directory
SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';

Directory created.

SQL>
SQL> grant read,write on directory BLOBDIR to bobo;

Grant succeeded.

SQL>



3.创建存储过程
SQL> connect bobo/bobo
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE bobo_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      bobo_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.


4.取出数据
SQL> host ls -l d:\oradata\Pic
total 7618
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg

SQL> exec bobo_dump_blob('ShaoLin.jpg','01.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:\oradata\Pic
total 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.jpg

SQL>
SQL> exec bobo_dump_blob('DaoYing.jpg','02.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:\oradata\Pic
total 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   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg