创建每日发送查询结果邮件的存储过程时出现编译错误

错误原因 #

1. 存储过程结构错误：你在存储过程的BEGIN执行块内嵌套了不必要的DECLARE块，导致游标和变量的作用域混乱，直接引发"emp_cursor is not visible"错误。正确的存储过程结构应该是在AS之后直接声明变量、游标，再进入BEGIN执行逻辑。
2. 引用不存在的字段：循环中使用l_emp_data_array(i).emp_data，但游标查询的字段里并没有emp_data，这会导致后续执行报错。
3. CLOB未初始化：l_clob默认值为NULL，直接拼接字符串会始终保持NULL，需要先初始化为空CLOB。

修复后的代码 #

create or replace PROCEDURE PROC_SEND_EMAIL AS 
    l_id NUMBER;
    l_clob CLOB := EMPTY_CLOB(); -- 初始化CLOB，避免拼接时为NULL
    -- 定义游标
    CURSOR emp_cursor IS
        SELECT
            COL_ID,COL_1,COL_2,COL_3,COL_4,COUNTRY_CODE,
            COL_5,COL_6,COL_7,COL_8,CREATE_DATE,
            VALIDATION_DATE,COMPLETION_DT,COL_9,CREATE_WEEK,CREATE_MONTH
        FROM
            V_VALIDATION_OF_COUNTRIES
        WHERE trunc(CREATE_DATE) >= trunc(sysdate-1) 
          AND trunc(CREATE_DATE) < trunc(sysdate)
        ORDER BY CREATE_DATE DESC;
    -- 定义基于游标行类型的数组
    TYPE emp_data_array IS TABLE OF emp_cursor%ROWTYPE INDEX BY PLS_INTEGER;
    l_emp_data_array emp_data_array;
BEGIN
    -- 批量获取游标数据到数组
    OPEN emp_cursor;
    FETCH emp_cursor BULK COLLECT INTO l_emp_data_array;
    CLOSE emp_cursor;

    -- 循环拼接CSV格式数据（字段用逗号分隔，行尾加换行符）
    FOR i IN 1..l_emp_data_array.COUNT LOOP
        l_clob := l_clob 
            || l_emp_data_array(i).COL_ID || ','
            || l_emp_data_array(i).COL_1 || ','
            || l_emp_data_array(i).COL_2 || ','
            || l_emp_data_array(i).COL_3 || ','
            || l_emp_data_array(i).COL_4 || ','
            || l_emp_data_array(i).COUNTRY_CODE || ','
            || l_emp_data_array(i).COL_5 || ','
            || l_emp_data_array(i).COL_6 || ','
            || l_emp_data_array(i).COL_7 || ','
            || l_emp_data_array(i).COL_8 || ','
            || TO_CHAR(l_emp_data_array(i).CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') || ','
            || TO_CHAR(l_emp_data_array(i).VALIDATION_DATE, 'YYYY-MM-DD HH24:MI:SS') || ','
            || TO_CHAR(l_emp_data_array(i).COMPLETION_DT, 'YYYY-MM-DD HH24:MI:SS') || ','
            || l_emp_data_array(i).COL_9 || ','
            || l_emp_data_array(i).CREATE_WEEK || ','
            || l_emp_data_array(i).CREATE_MONTH
            || CHR(13) || CHR(10); -- Windows标准换行符
    END LOOP;

    -- 发送邮件并附加CSV文件
    l_id := APEX_MAIL.SEND(
        p_to => 'name@mail.com',
        p_from => 'name_from@mail.com',
        p_subj => 'Report daily',
        p_body => 'Please review the attachment.',
        p_body_html => '<b>Please</b> review the attachment'
    );

    APEX_MAIL.ADD_ATTACHMENT(
        p_mail_id => l_id,
        p_attachment => l_clob,
        p_filename => 'mydata.csv',
        p_mime_type => 'text/csv'
    );

    COMMIT;
END PROC_SEND_EMAIL;
/

额外说明 #

• 日期格式化：将日期类型字段转换为统一字符串格式，避免CSV中出现非标准日期值导致解析错误。
• 特殊字符处理：如果字段包含逗号、双引号等特殊字符，需要用双引号包裹对应字段，否则会破坏CSV结构（例如：|| '"' || l_emp_data_array(i).COL_1 || '"' || ','）。
• APX邮件配置：确保Oracle APEX环境已正确配置邮件服务器，否则邮件无法正常发送。

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