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

王朝mssql·作者佚名  2008-06-01
窄屏简体版  字體: |||超大  

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

1.首先,确认现有对象

SQL> col fdesc for a30

SQL> select fid,fname,fdesc from eygle_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:oradataPic';

Directory created.

SQL>

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

Grant succeeded.

SQL>

3.创建存储过程

SQL> connect eygle/eygle

Connected.

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.

4.最后取出数据

SQL> host ls -l d:oradataPic

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 eygle_dump_blob('ShaoLin.jpg','01.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:oradataPic

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 eygle_dump_blob('DaoYing.jpg','02.jpg')

PL/SQL procedure successfully completed.

SQL> host ls -l d:oradataPic

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航