使用PLSQL从数据库中读取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 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;

bobo_dump_blob (piname

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