PL/SQL SELECT結果をCSV出力するストアドファンクション（動的SQL使用）

/* ========================================================================== */
/* pSQLstring : CSVファイルに出力するデータを取得するSELECT文
/* pOutputDirName : CSVファイルを出力するディレクトリ
/* pOutputFileName: CSVファイル名
/* pMessage : エラー時にメッセージが設定されるOUTパラメータ
/* 制限事項 : SELECTの結果はすべてVARCHARかCHARであること
/* : データ中に「,」が存在する場合の考慮はしていない
/* : UTL_FILEパッケージ、DBMS_SQLパッケージ使用
/* ========================================================================== */
CREATE OR REPLACE FUNCTION GON_TEST(
pSQLstring IN VARCHAR2
,pOutputDirName IN VARCHAR2
,pOutputFileName IN VARCHAR2
,pMessage OUT VARCHAR2
) RETURN NUMBER
IS
TYPE_VARCHAR CONSTANT NUMBER := 1;
TYPE_CHAR CONSTANT NUMBER := 96;

TYPE tColArray IS TABLE OF VARCHAR2(32767);

AppException EXCEPTION;
vFileHandle UTL_FILE.FILE_TYPE;
vRecTab DBMS_SQL.DESC_TAB;
vCursorID NUMBER;
vColCount NUMBER;
vIgnore NUMBER;
vFetchValue tColArray;
vOutputString VARCHAR2(32767);

BEGIN

vCursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(vCursorID, pSQLstring, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(vCursorID, vColCount, vRecTab);

FOR i IN vRecTab.FIRST .. vRecTab.LAST LOOP
IF (vRecTab(i).COL_TYPE != TYPE_VARCHAR AND vRecTab(i).COL_TYPE != TYPE_CHAR) THEN
pMessage := 'SELECTで取得する項目はVARCHARかCHARにしてください';
RAISE AppException;
END IF;
END LOOP;

vFetchValue := tColArray();
vFetchValue.EXTEND(vColCount);
FOR i IN vFetchValue.FIRST .. vFetchValue.LAST LOOP
DBMS_SQL.DEFINE_COLUMN(vCursorID, i, vFetchValue(i), vRecTab(i).COL_MAX_LEN);
END LOOP;

vIgnore := DBMS_SQL.EXECUTE(vCursorID);
vFileHandle := UTL_FILE.FOPEN(pOutputDirName, pOutputFileName, 'w', 32767);
LOOP
IF (DBMS_SQL.FETCH_ROWS(vCursorID) > 0) THEN
FOR i IN vFetchValue.FIRST .. vFetchValue.LAST LOOP
DBMS_SQL.COLUMN_VALUE(vCursorID, i, vFetchValue(i));
vOutputString := vOutputString || ',' || vFetchValue(i);
END LOOP;
UTL_FILE.PUT_LINE(vFileHandle, SUBSTR(vOutputString, 2));
vOutputString := NULL;
ELSE
EXIT;
END IF;
END LOOP;
UTL_FILE.FCLOSE(vFileHandle);
DBMS_SQL.CLOSE_CURSOR(vCursorID);

RETURN(0);

EXCEPTION
WHEN AppException THEN
IF (UTL_FILE.IS_OPEN(vFileHandle)) THEN
UTL_FILE.FCLOSE(vFileHandle);
END IF;
IF (DBMS_SQL.IS_OPEN(vCursorID)) THEN
DBMS_SQL.CLOSE_CURSOR(vCursorID);
END IF;
RETURN(-1);
WHEN OTHERS THEN
IF (UTL_FILE.IS_OPEN(vFileHandle)) THEN
UTL_FILE.FCLOSE(vFileHandle);
END IF;
IF (DBMS_SQL.IS_OPEN(vCursorID)) THEN
DBMS_SQL.CLOSE_CURSOR(vCursorID);
END IF;
pMessage := SQLERRM;
RETURN(-1);
END;
/

/* 呼び出しのサンプル */
DECLARE

vSQLstring VARCHAR2(1000) := ''; -- CSV出力するデータを取得するSELECT文
vOutputDirName VARCHAR2(1000) := ''; -- CSVファイルを出力するディレクトリのパス
vOutputFileName VARCHAR2(1000) := ''; -- CSVファイル名
vMessage VARCHAR2(1000); -- エラーメッセージを受け用の変数
vRet NUMBER; -- エラーコード受け用の変数

BEGIN

vRet := GON_TEST(vSQLstring, vOutputDirName, vOutputFileName, vMessage);

DBMS_OUTPUT.PUT_LINE(vRet);
DBMS_OUTPUT.PUT_LINE(vMessage);

END;
/