oracle 存储过程操作blob

为什么80%的码农都做不了架构师？>>>   

一、授权用户及创建目录

SQL> grant CREATE ANY DIRECTORY to user;

Grant succeeded.

SQL> conn user/password

Connected.

SQL> create or replace directory BLOBDIR as '/home/oracle/picDir';

Directory created.

二、导出文件procedure

create or replace procedure photo_dump(IDENTITYID in varchar2,
                                       filename   in varchar2) is
  l_file     UTL_FILE.FILE_TYPE;
  l_buffer   RAW(32767);
  l_amount   BINARY_INTEGER := 32767;
  l_pos      INTEGER := 1;
  l_blob     BLOB;
  l_blob_len INTEGER;
begin
  SELECT key_value
    INTO l_blob
    FROM blob_map_t
   WHERE key_id = IDENTITYID;
  l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
  l_file     := UTL_FILE.FOPEN('BLOBDIR',
                               filename,
                               'wb', 
                               32767);
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_FILE.FCLOSE(l_file);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
    END IF;
    RAISE;
end photo_dump;

三、调用导出procedure

create or replace procedure out_put_photo is
  IDENTITYID varchar2(300);
  FILENAME varchar2(300);
  cursor cur is
    select key_id, key_name
      from blob_map_t;
  cur_result cur%rowtype;
begin
  if cur%isopen = false then
    open cur;
  end if;
  loop
    fetch cur
      into cur_result;
    exit when cur%notfound;
    IDENTITYID := cur_result.key_id;
    FILENAME := cur_result.key_name;
    photo_dump(identityid => IDENTITYID, filename => FILENAME || '.jpg');
  end loop;
  close cur;
end out_put_photo;

四、导出执行

begin
  -- Call the procedure
  out_put_photo;
end;

转载于:https://my.oschina.net/jinp/blog/215345