Oracle寫本地檔案

Oracle寫本地檔案是指寫到執行Oracle的主機上，而不是執行該指令碼的機器上。
說起來有點拗口，實際上就是無論在哪裡執行這個過程，生成的檔案始終都是在伺服器上的。
下面過程實現了這個功能：
logdir是指檔案存放路徑。有Oracle的directory指定：
create or replace directory log_dir  as '/oracle/admin/orcl';

建立帶有clob欄位的表：
create table testclob(obj_type varchar2(255), obj_content clob);

插入測試資料：
insert into testclob(obj_type, obj_content)
select 'PROCEDURE', dbms_metadata.get_ddl('PROCEDURE', o.object_name)
from dba_objects o
where o.owner = user
and o.object_type = 'PROCEDURE'

建立儲存過程：
create or replace procedure sp_writelog2file(logdir varchar2,
                                             filename varchar2,
                                             writemode char := 'W') as
    type tbl_result is table of varchar2(2000) index by pls_integer;
    v_res tbl_result;
    type tbl_clob is table of clob index by pls_integer;
    v_clobs   tbl_clob;

    v_filename   varchar2(255) := filename;
    v_logdir     varchar2(255) := logdir;
    v_buffer     pls_integer := 2000;
    v_offset     pls_integer := 1;
    v_filehandle utl_file.file_type;

    function f_readclob(varclob clob)
    return tbl_result as
        v_maxbuff pls_integer := 2000;
        v_cloblen pls_integer := length(varclob);
        v_result tbl_result;
        v_buffer pls_integer := v_maxbuff;
        v_offset pls_integer := 1;
        v_nextpos pls_integer := 1;
        v_prevpos pls_integer := 1;
        v_maxstep pls_integer := 20;
        v_nth    pls_integer := v_maxstep;
    begin
        while v_nextpos <> 0 loop
            v_nextpos := dbms_lob.instr(varclob, chr(10), 1, v_nth);
            v_buffer  := (case when v_nextpos = 0 then v_cloblen else v_nextpos end) - v_prevpos;

            if (v_buffer > v_maxbuff and v_nextpos <> 0) then
                v_nth := v_nth - 3;/*超過最大緩衝區，指標退3個*/
            elsif (v_buffer < 3*v_maxbuff/4 and v_nextpos <> 0) then
                v_nth := v_nth + 3;/*未達最大緩衝區的3/4，指標進3個*/
            else
                dbms_lob.read(varclob, v_buffer, v_offset, v_result(nvl(v_result.last, 0) + 1));
                v_prevpos := v_nextpos;
                v_nth := v_nth + v_maxstep;
                v_offset := v_offset + v_buffer;
            end if;
        end loop;
        return v_result;
    end f_readclob;
begin
    v_filehandle := utl_file.fopen(v_logdir, v_filename, writemode);
    if(utl_file.is_open(v_filehandle)) then
        select t2.obj_content
          bulk collect into v_clobs
          from testclob t2;
        for i in 1 .. v_clobs.count loop
            v_res := f_readclob(v_clobs(i));
            for j in 1 .. v_res.count loop
                utl_file.put_line(v_filehandle, v_res(j));
            end loop;
        end loop;
    end if;
    utl_file.fclose(v_filehandle);
exception when others then
    utl_file.fclose(v_filehandle);
end sp_writelog2file;

呼叫儲存過程，將testclob的內容寫到主機上的檔案中：
call sp_writelog2file('LOG_DIR', 'lyon.txt');

對於clob物件的讀取，採用了分段擷取的演算法。擷取標識為換行符（chr(10)）。
每次步長為20個換行符間隔。假設每次擷取長度最大值為N(這裡N=2000)。該間隔區間內，如果字元數範圍在[N*3/4, N]之間，則直接擷取。
如果小於3/4 N長度，則指標標識向前推3個換行符間隔。如果大於N，則向後退3個換行符間隔。保證擷取的長度始終在3/4-1個N之間。