UTL_FILE包的簡單例子

CREATE OR REPLACE PROCEDURE P_WRITE_FILE
(
P_TABLE_NAME IN VARCHAR2,
P_COLUMN_LIST IN VARCHAR2 DEFAULT NULL,
P_WHERE_STR IN VARCHAR2 DEFAULT NULL,
P_SEP IN VARCHAR2, --域分隔符
P_END IN VARCHAR2 --行結尾符
) AS
V_FILE UTL_FILE.FILE_TYPE;
V_BUFFER VARCHAR2(32767);
V_RESULT VARCHAR2(32767);
C_RESULT SYS_REFCURSOR;

--c_a char(1);
BEGIN
V_FILE := UTL_FILE.FOPEN('D_OUTPUT', P_TABLE_NAME || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv','w', 32767);
IF P_COLUMN_LIST IS NULL THEN
FOR C_COLUMN IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = upper(P_TABLE_NAME)) LOOP
V_BUFFER := V_BUFFER || C_COLUMN.COLUMN_NAME || ',';
END LOOP;
V_BUFFER := RTRIM(V_BUFFER, ',');
ELSE
V_BUFFER := P_COLUMN_LIST;
END IF;
UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);
--V_BUFFER := REPLACE(V_BUFFER, ',', '|| ''","'' ||');
V_BUFFER := REPLACE(V_BUFFER, ',', '|| '','' ||');
V_BUFFER := REPLACE(V_BUFFER, ',', P_SEP);
--V_BUFFER := V_BUFFER ||'||'||''''||P_END||''''||' ';
if P_END is not null then
V_BUFFER := V_BUFFER||'||'''||P_SEP ||P_END||'''';
end if;
--dbms_output.enable(1000000);
dbms_output.put_line(substr(V_BUFFER,1,255));
dbms_output.put_line(substr(V_BUFFER,length(V_BUFFER)-255,255));
--V_BUFFER := 'SELECT ''"'' ||' || V_BUFFER || '||''"'' RESULT FROM ' || P_TABLE_NAME;
V_BUFFER := 'SELECT '||V_BUFFER||' RESULT FROM ' || P_TABLE_NAME;
IF P_WHERE_STR IS NOT NULL THEN
IF SUBSTR(LTRIM(P_WHERE_STR), 1, 5) != 'WHERE' THEN
V_BUFFER := V_BUFFER || ' WHERE';
END IF;
V_BUFFER := V_BUFFER || ' ' || P_WHERE_STR;
END IF;
OPEN C_RESULT FOR V_BUFFER;
LOOP
FETCH C_RESULT INTO V_RESULT;
EXIT WHEN C_RESULT%NOTFOUND;
UTL_FILE.PUT_LINE(V_FILE, V_RESULT);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
END;

用這個就可以用sqlldr倒入資料或者可以倒入到別的資料庫了!!