确认现有对象
SQL> col fdesc for a
SQL> select fidfnamefdesc from eygle_blob;
FID FNAMEFDESC
ShaoLinjpg少林寺康熙手书
DaoYingjpg倒映
创建存储Directory
SQL> connect / as sysdba
Connected
SQL> create or replace directory BLOBDIR as D:\oradata\Pic;
Directory created
SQL>
SQL> grant readwrite on directory BLOBDIR to eygle;
Grant succeeded
SQL>
创建存储过程
SQL> connect eygle/eygle
Connected
SQL>
SQL> CREATE OR REPLACE PROCEDURE eygle_dump_blob (piname varcharponame varchar) IS
l_fileUTL_FILEFILE_TYPE;
l_bufferRAW();
l_amountBINARY_INTEGER := ;
l_pos INTEGER := ;
l_blobBLOB;
l_blob_lenINTEGER;
BEGIN
SELECT FPIC
INTOl_blob
FROMeygle_blob
WHEREFNAME = piname;
l_blob_len := DBMS_LOBGETLENGTH(l_blob);
l_file := UTL_FILEFOPEN(BLOBDIRponamewb );
WHILE l_pos < l_blob_len LOOP
DBMS_LOBREAD (l_blob l_amount l_pos l_buffer);
UTL_FILEPUT_RAW(l_file l_buffer TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILEFCLOSE(l_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILEIS_OPEN(l_file) THEN
UTL_FILEFCLOSE(l_file);
END IF;
RAISE;
END;
/
Procedure created
取出数据
SQL> host ls l d:\oradata\Pic
total
rwxrwxrwa gqgai None Apr : DaoYingjpg
rwxrwxrwa gqgai None Apr : ShaoLinjpg
SQL> exec eygle_dump_blob(ShaoLinjpgjpg)
PL/SQL procedure successfully completed
SQL> host ls l d:\oradata\Pic
total
rwxrwxrwa AdministratorsSYSTEM Apr : jpg
rwxrwxrwa gqgai None Apr : DaoYingjpg
rwxrwxrwa gqgai None Apr : ShaoLinjpg
SQL>
SQL> exec eygle_dump_blob(DaoYingjpgjpg)
PL/SQL procedure successfully completed
SQL> host ls l d:\oradata\Pic
total
rwxrwxrwa AdministratorsSYSTEM Apr : jpg
rwxrwxrwa AdministratorsSYSTEM Apr : jpg
rwxrwxrwa gqgai None Apr : DaoYingjpg
rwxrwxrwa gqgai None Apr : ShaoLinjpg
