参考にさせていただいたページ
忘れっぽいエンジニアのオラクルＳＱＬリファレンス＠IT Database Exp※CLOBを大量に読み込んでるので当然といえば当然ですがORA-04030が出ました。
CREATE OR REPLACE PROCEDURE DL_HOGE
( P_HOGE_YEAR IN VARCHAR2 )
AS
-- ファイルハンドル
FILE_HAND UTL_FILE.FILE_TYPE;
TYPE DATA_PRE_TYPE IS TABLE OF VARChAR2(32767)
INDEX BY PLS_INTEGER;
DATA_PRE_TAB DATA_PRE_TYPE;
TYPE DATA_CLOB_TYPE IS TABLE OF CLOB
INDEX BY PLS_INTEGER;
DATA_CLOB_TAB DATA_CLOB_TYPE;
TYPE DATA_POS_TYPE IS TABLE OF VARChAR2(2)
INDEX BY PLS_INTEGER;
DATA_POS_TAB DATA_POS_TYPE;
REC_DL VARCHAR2(32767);
V_TABLE_NAME VARCHAR2(32) :='hoge_mail';
V_NAME VARCHAR2 (100) := 'hoge.tsv';
V_CLOB_DATA CLOB;
V_CLOB_BUFFER VARCHAR2(32767);
V_FILE_SIZE INTEGER;
V_START_POINT INTEGER := 1;
V_WRITE_SIZE INTEGER := 32767;
BEGIN
-- 日付フォーマット設定
EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';
-- ファイルハンドルのOPEN
FILE_HAND:=UTL_FILE.FOPEN_NCHAR('DL_DIR',V_NAME,'w',32767);
-- データを配列にいれる
SELECT
HOGE_ID||','||
HOGE_TITLE||',' ,
HOGE_CLOB ,
','||INSERT_DATE
BULK COLLECT INTO DATA_PRE_TAB,DATA_CLOB_TAB,DATA_POS_TAB
FROM HOGE_MAIL
WHERE INSERT_DATE >= TO_DATE(P_HOGE_YEAR,'YYYY')
;
/* 対象のデータが存在する場合 */
IF DATA_PRE_TAB.count > 0 THEN
FOR i IN DATA_PRE_TAB.FIRST..DATA_PRE_TAB.LAST LOOP
/* ファイルハンドルにPREいれる */
UTL_FILE.PUT_NCHAR(FILE_HAND,DATA_PRE_TAB(i));
/* CLOBデータのサイズを取得する */
V_FILE_SIZE := dbms_lob.getlength(DATA_CLOB_TAB(i));
WHILE V_START_POINT < V_FILE_SIZE loop
/* 最後の書込みの場合、書込みサイズを残りサイズに合わせる */
if V_START_POINT + V_WRITE_SIZE > V_FILE_SIZE then
V_WRITE_SIZE := V_FILE_SIZE - V_START_POINT +1;
end if;
/* CLOBデータを先頭からCLOBデータサイズ読み込み、「V_CLOB_BUFFER」に代入 */
dbms_lob.read(DATA_CLOB_TAB(i), V_WRITE_SIZE, V_START_POINT, V_CLOB_BUFFER);
/* 「V_CLOB_BUFFER」に代入されたCLOBデータをファイル出力する */
utl_file.put_nchar(file_hand, V_CLOB_BUFFER);
/* 書込み開始位置を進める */
V_START_POINT := V_START_POINT + V_WRITE_SIZE;
end loop;
utl_file.fflush(FILE_HAND);
V_START_POINT :=1;
/* ファイルハンドルにPOSいれる */
UTL_FILE.PUT_LINE_NCHAR(FILE_HAND,DATA_POS_TAB(i));
END LOOP;
END IF;
/* ファイルハンドルのクローズ */
UTL_FILE.FCLOSE(FILE_HAND);
END DL_HOGE;
