ORACLE發送帶附件郵件的二三事之一

標籤：

 

在oracle使用過程中，我們可以通過pl/sql產生資料檔案，也可以通過spool on spool off產生，但某些環境下，我們需要通過預存程序處理資料，資料處理完，需要自動產生資料檔案，手工匯出產生方式便不適用。

下面我們嘗試這樣處理，能夠滿足我們的需求

第一步：發送帶附件郵件必須需可以產生附件，檢查是系統是否有許可權

select * from all_directories

預設系統配置有“DATA_PUMP_DIR”，如果沒有聯絡dba建立

create directory DATA_PUMP_DIR as   “ /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/”

第二步：定義處理過程，過程輸出是逗號分隔的文本，包括sql欄位名稱

輸入為：查詢的sql語句，存放路徑，檔案名稱

處理過程：

CREATE OR REPLACE PROCEDURE "EXP_DATA"
(
        P_QUERY IN VARCHAR2,              -- sql語句 例子: ‘select * from TEST‘
        P_DIR IN VARCHAR2,                -- 目錄  用這個命令查看目錄show parameter utl_file_dir
        P_FILENAME IN VARCHAR2            --  要產生的檔案名稱
    )
    IS
        L_OUTPUT UTL_FILE.FILE_TYPE;
        L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
        L_COLUMNVALUE VARCHAR2(4000);
        L_STATUS INTEGER;
        L_COLCNT NUMBER := 0;
        L_SEPARATOR VARCHAR2(1);
        L_DESCTBL DBMS_SQL.DESC_TAB;
        P_MAX_LINESIZE NUMBER := 32000;
    BEGIN
        --OPEN FILE
        L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, ‘W‘, P_MAX_LINESIZE);
        --DEFINE DATE FORMAT
        EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=‘‘YYYY-MM-DD HH24:MI:SS‘‘‘;
        --OPEN CURSOR
        DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
        DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
        --DUMP TABLE COLUMN NAME
      FOR I IN 1 .. L_COLCNT LOOP
           UTL_FILE.PUT( L_OUTPUT,L_DESCTBL(I).COL_NAME);
           if i<L_COLCNT then
               UTL_FILE.PUT(L_OUTPUT,‘,‘);
           end if;
           DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
           L_SEPARATOR := ‘‘;
       END LOOP;
       UTL_FILE.NEW_LINE( L_OUTPUT );
        --EXECUTE THE QUERY STATEMENT
        L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
        --DUMP TABLE COLUMN VALUE
        WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
            L_SEPARATOR := ‘,‘;
            FOR I IN 1 .. L_COLCNT LOOP
                if i< L_COLCNT then
                 begin
                DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
                UTL_FILE.PUT( L_OUTPUT,
                TRIM(BOTH ‘ ‘ FROM REPLACE(L_COLUMNVALUE,‘,‘,‘ ‘)));
                UTL_FILE.PUT(L_OUTPUT,‘,‘);
                end;
                else
                  begin
                 DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
                UTL_FILE.PUT( L_OUTPUT,
                TRIM(BOTH ‘ ‘ FROM REPLACE(L_COLUMNVALUE,‘,‘,‘ ‘)));
                  end;
                end if;
                L_SEPARATOR := ‘,‘;
            END LOOP;
            UTL_FILE.NEW_LINE( L_OUTPUT );
        END LOOP;
        --CLOSE CURSOR
        DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
        --CLOSE FILE
        UTL_FILE.FCLOSE( L_OUTPUT );
END;

第三步：測試輸出,特別注意如果是linux環境，檔案名稱對大小寫敏感，即輸出檔案名與使用引用的檔案名稱必須一致。

exec EXP_DATA(‘select xh 序號,hm號碼,flag 標誌 from test‘,‘DATA_PUMP_DIR‘,‘TEST.CSV‘);

ORACLE發送帶附件郵件的二三事之一