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; /