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:\oradata\Pic';
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:\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 eygle_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 eygle_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