1.首先，确认现有对象
SQL> col fdesc for a30SQL> select fid,fname,fdesc from eygle_blob; FID FNAME FDESC------------------------ ------------------------------ 1 ShaoLin.jpg 少林寺-康熙手书 2 DaoYing.jpg 倒映
2.创建存储Directory
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>
3.创建存储过程
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 28UTL_FILE.FCLOSE(l_file); 29 END IF; 30 RAISE; 31 END; 32 /Procedure created.
4.最后取出数据
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 SYSTEM1768198 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 SYSTEM1768198 Apr 26 07:16 01.jpg-rwxrwxrwa 1 Administrators SYSTEM2131553 Apr 26 07:19 02.jpg-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa
