SQLLoaderのCTLファイル作成Function

tsv用

CREATE OR REPLACE FUNCTION MAKE_CTL
(
	P_TABLE_NAME IN VARCHAR2
)
--RETURN CLOB
RETURN VARCHAR2
IS
	CURSOR CUR_TABLE_COLUMN(V_TABLE_NAME VARCHAR2) IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = V_TABLE_NAME;
--	CTL_STR CLOB;
	CTL_STR VARCHAR2(4000);
	CR CONSTANT CHAR(1) := CHR(13);
	LF CONSTANT CHAR(1) := CHR(10);
	NL CONSTANT CHAR(2) := CR || LF;
BEGIN
	FOR COL IN CUR_TABLE_COLUMN(P_TABLE_NAME) LOOP
		IF CUR_TABLE_COLUMN%ROWCOUNT > 1 THEN
			CTL_STR := CTL_STR || ',';
		ELSE
			CTL_STR := CTL_STR || ' ';
		END IF;
		CTL_STR := CTL_STR || COL.COLUMN_NAME || NL;
	END LOOP;

	CTL_STR :=
		         'OPTIONS(SKIP=1,ERRORS=-1)'
		|| NL || 'LOAD DATA'
		|| NL || 'REPLACE'
		|| NL || 'INTO TABLE ' || P_TABLE_NAME
		|| NL || 'FIELD TERMINATED BY X''09'''
		|| NL || 'TRAILING NULLCOLS'
		|| NL || '('
		|| NL || CTL_STR
		|| NL || ')';

	RETURN CTL_STR;
END;
/