看了yangtingkun的这个帖子,我做了一下改进,加入了域分隔符和行结尾符

GRANT READ[,WRITE] ON DIRECTORY directory TO username;

)

SQL> CREATE DIRECTORY D_OUTPUT AS 'E:';

目录已创建。

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 --行结尾符) ASV_FILE UTL_FILE.FILE_TYPE;V_BUFFER VARCHAR2(32767);V_RESULT VARCHAR2(32767);C_RESULT SYS_REFCURSOR;

--c_a char(1);

BEGINV_FILE := UTL_FILE.FOPEN('D_OUTPUT', P_TABLE_NAME || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv','w', 32767);IF P_COLUMN_LIST IS NULL THENFOR C_COLUMN IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = upper(P_TABLE_NAME)) LOOPV_BUFFER := V_BUFFER || C_COLUMN.COLUMN_NAME || ',';END LOOP;V_BUFFER := RTRIM(V_BUFFER, ',');ELSEV_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 thenV_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 THENIF SUBSTR(LTRIM(P_WHERE_STR), 1, 5) != 'WHERE' THENV_BUFFER := V_BUFFER || ' WHERE';END IF;V_BUFFER := V_BUFFER || ' ' || P_WHERE_STR;END IF;OPEN C_RESULT FOR V_BUFFER;LOOPFETCH 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倒入数据或者可以倒入到别的数据库了!!

ref: