如何使用 pl/sql工具 修改blob类型的数据

Question

如题所述

Answer 1

如何使用 pl/sql工具 修改blob类型的数据
使用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;