リスト2　BLOBデータを出力するPL/SQLサンプル

1 set serveroutput on
2 declare
3         v_blob_locater        blob;
4         v_offset                integer := 1;
5         v_buffer                long raw;
6
7         v_file_buffer_size     integer := 32000;
8         v_amount              integer := 32000;
9
10         v_totalsize            integer;
11         v_filetype             utl_file.file_type;
12         v_filename            varchar2(1000) := 'maguro_out.jpg';
13         v_openmode         varchar2(2) := 'wb';
14
15 begin
16         select data into v_blob_locater from test_blob_data where id = 1;
17         v_totalsize := dbms_lob.getlength(v_blob_locater);
18         v_filetype := utl_file.fopen('LOB_DATA_PATH', v_filename, v_openmode, 19 v_file_buffer_size);
20
21         while v_offset < v_totalsize loop
22                 if v_offset + v_amount > v_totalsize then
23                         v_amount := v_totalsize - v_offset + 1;
24                 end if;
25                 dbms_lob.read(
26                         v_blob_locater,
27                         v_amount,
28                         v_offset,
29                         v_buffer
30                 );
31
32                 utl_file.put_raw(
33                         v_filetype,
34                         v_buffer,
35                         true
36                 );
37                 v_offset := v_offset + v_amount;
38                 dbms_output.put_line ( 'Offset : ' || v_offset );
39         end loop;
40
41         utl_file.fflush(v_filetype);
42         utl_file.fclose(v_filetype);
43
44 end;
45 /