无目录环境下PL/SQL查询结果直接以CSV格式邮件发送方案求助

Hey Sachin, totally get your predicament—you don’t need to save a CSV file to a directory first! We can generate the CSV content directly in memory and send it as an email attachment using Oracle’s built-in packages. Here’s a practical, step-by-step solution:

Prerequisites #

First, make sure your database user has the necessary permissions:

• Execute access to UTL_MAIL (for sending emails)
• Execute access to DBMS_SQL (for dynamic query handling)
• Your Oracle instance has an SMTP server configured (check the UTL_MAIL.SERVER parameter)

If you don’t have these permissions, ask your DBA to run:

GRANT EXECUTE ON UTL_MAIL TO your_username;
GRANT EXECUTE ON DBMS_SQL TO your_username;

PL/SQL Procedure to Generate & Send CSV #

This procedure dynamically runs your query, builds the CSV content in memory, and sends it as an attachment—no file system required:

CREATE OR REPLACE PROCEDURE send_query_results_as_csv (
    p_recipient    IN VARCHAR2,
    p_subject      IN VARCHAR2,
    p_query        IN VARCHAR2
) AS
    l_csv_content  CLOB := EMPTY_CLOB();
    l_cursor       SYS_REFCURSOR;
    l_dbms_cursor  PLS_INTEGER;
    l_col_cnt      PLS_INTEGER;
    l_desc_tbl     DBMS_SQL.DESC_TAB;
    l_col_value    VARCHAR2(4000);
    l_separator    CONSTANT VARCHAR2(1) := ',';
    l_quote        CONSTANT VARCHAR2(1) := '"';
BEGIN
    -- Open cursor for the input query
    OPEN l_cursor FOR p_query;
    l_dbms_cursor := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor);

    -- Get column metadata to build CSV headers
    DBMS_SQL.DESCRIBE_COLUMNS(l_dbms_cursor, l_col_cnt, l_desc_tbl);
    
    -- Add column names as CSV headers
    FOR i IN 1..l_col_cnt LOOP
        IF i > 1 THEN
            l_csv_content := l_csv_content || l_separator;
        END IF;
        l_csv_content := l_csv_content || l_quote || l_desc_tbl(i).col_name || l_quote;
    END LOOP;
    l_csv_content := l_csv_content || CHR(10) || CHR(13); -- New line for rows

    -- Define columns for fetching
    FOR i IN 1..l_col_cnt LOOP
        DBMS_SQL.DEFINE_COLUMN(l_dbms_cursor, i, l_col_value, 4000);
    END LOOP;

    -- Fetch each row and build CSV rows
    WHILE DBMS_SQL.FETCH_ROWS(l_dbms_cursor) > 0 LOOP
        FOR i IN 1..l_col_cnt LOOP
            IF i > 1 THEN
                l_csv_content := l_csv_content || l_separator;
            END IF;
            DBMS_SQL.COLUMN_VALUE(l_dbms_cursor, i, l_col_value);
            
            -- Handle special characters (comma/quote) per CSV standards
            IF l_col_value LIKE '%' || l_separator || '%' OR l_col_value LIKE '%' || l_quote || '%' THEN
                l_csv_content := l_csv_content || l_quote || REPLACE(l_col_value, l_quote, l_quote || l_quote) || l_quote;
            ELSE
                l_csv_content := l_csv_content || l_col_value;
            END IF;
        END LOOP;
        l_csv_content := l_csv_content || CHR(10) || CHR(13);
    END LOOP;

    -- Clean up cursors
    DBMS_SQL.CLOSE_CURSOR(l_dbms_cursor);
    CLOSE l_cursor;

    -- Send email with CSV attachment
    UTL_MAIL.send_attach_varchar2(
        sender        => 'your_email@yourdomain.com', -- Replace with your sender email
        recipients    => p_recipient,
        subject       => p_subject,
        message       => 'Please find the attached query results in CSV format.',
        attachment    => l_csv_content,
        att_inline    => FALSE,
        att_filename  => 'query_results.csv',
        att_mime_type => 'text/csv'
    );

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        RAISE; -- Re-throw to propagate the error
END;
/

How to Use the Procedure #

Call the procedure with your recipient email, subject, and target query:

BEGIN
    send_query_results_as_csv(
        p_recipient => 'sachin@example.com',
        p_subject   => 'Q3 Sales Data',
        p_query     => 'SELECT region, product, sale_amount, sale_date FROM sales WHERE sale_date BETWEEN ''01-JUL-2024'' AND ''30-SEP-2024'''
    );
END;
/

Key Details #

• Dynamic Query Handling: The procedure works with any SELECT query—no need to hardcode columns.
• CSV Compliance: Automatically handles fields with commas or double quotes by wrapping them in quotes and escaping internal quotes (per RFC 4180 standards).
• In-Memory Generation: All CSV content is built in a CLOB, so no file system writes are needed.

内容的提问来源于stack exchange，提问作者Sachin Vaidya