Oracle存储过程执行报错：ORA-00933 SQL命令未正确结束

问题场景 #

存储过程中通过UNION ALL合并两个查询结果时，触发如下错误：

Error(331,2): PL/SQL: ORA-00933: SQL command not properly ended

涉及的SQL语句如下：

SELECT 
    jb.span_id,
    jb.maintenancezonecode,
    jbp.job_progress_id AS req_id,
    jb.reofferflag,
    modified_by AS last_update_by,
    sm.status_name,
    TO_DATE(SYSDATE) - TO_DATE(jbp.ums_group_ass_to_date) AS pending_days
FROM   
    tbl_fiber_inv_jobs jb
INNER JOIN
    tbl_fiber_inv_job_progress jbp ON jb.job_id = jbp.job_id
INNER JOIN
    tbl_fiber_inv_status_master sm ON jbp.status_id = sm.status_id
WHERE  
    jb.maintenancezonecode = pmaintzonecode
    AND ums_group_ass_to_name = pusertype
    AND jb.span_type = pspantype
    AND jbp.job_progress_flag = 1
ORDER BY 
    jbp.job_progress_id DESC

UNION ALL
  
SELECT
    TO_CHAR(TRIM(rj_span_id)) AS SPAN_ID, 
    TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
FROM 
    NE.MV_SPAN@ne
WHERE
    LENGTH(TRIM(rj_span_id)) = 21
    AND Regexp_like (TRIM(rj_span_id),
                     'SP(N|Q|R|S).*+_(AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$', 'i')
    AND NOT Regexp_like (Nvl (rj_intracity_link_id, '-'), '_(9)', 'i')
    AND inventory_status_code = 'IPL'
    AND rj_maintenance_zone_code = pmaintzonecode;

错误原因 #

1. UNION ALL前的ORDER BY语法违规：Oracle中，UNION ALL合并的单个子查询不能独立使用ORDER BY，合并操作会重新组织结果集，子查询的排序会被忽略，同时这种写法直接触发语法错误（ORA-00933）。
2. 列数不匹配：第一个查询返回7列，第二个查询仅返回2列，UNION/UNION ALL要求两个子查询的列数完全一致，且对应列的数据类型兼容，这是后续执行必然会遇到的逻辑错误。

解决方法 #

步骤1：修正语法错误 #

移除第一个子查询末尾的ORDER BY语句，若需要对最终合并结果排序，将ORDER BY放在整个语句的最后。

步骤2：匹配列数与数据类型 #

调整第二个子查询，补充缺失的列（用NULL或合适的默认值填充），确保两个子查询的列数一致，且对应列的数据类型兼容。

修正后的完整SQL示例 #

SELECT 
    jb.span_id,
    jb.maintenancezonecode,
    jbp.job_progress_id AS req_id,
    jb.reofferflag,
    modified_by AS last_update_by,
    sm.status_name,
    TO_DATE(SYSDATE) - TO_DATE(jbp.ums_group_ass_to_date) AS pending_days
FROM   
    tbl_fiber_inv_jobs jb
INNER JOIN
    tbl_fiber_inv_job_progress jbp ON jb.job_id = jbp.job_id
INNER JOIN
    tbl_fiber_inv_status_master sm ON jbp.status_id = sm.status_id
WHERE  
    jb.maintenancezonecode = pmaintzonecode
    AND ums_group_ass_to_name = pusertype
    AND jb.span_type = pspantype
    AND jbp.job_progress_flag = 1

UNION ALL
  
SELECT
    TO_CHAR(TRIM(rj_span_id)) AS SPAN_ID, 
    TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE,
    NULL AS req_id,
    NULL AS reofferflag,
    NULL AS last_update_by,
    NULL AS status_name,
    NULL AS pending_days
FROM 
    NE.MV_SPAN@ne
WHERE
    LENGTH(TRIM(rj_span_id)) = 21
    AND Regexp_like (TRIM(rj_span_id),
                     'SP(N|Q|R|S).*+_(AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$', 'i')
    AND NOT Regexp_like (Nvl (rj_intracity_link_id, '-'), '_(9)', 'i')
    AND inventory_status_code = 'IPL'
    AND rj_maintenance_zone_code = pmaintzonecode
ORDER BY req_id DESC; -- 最终结果排序放在语句末尾

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