Oracle PL/SQL函数报ORA-00936缺失表达式，请求排查

开发PL/SQL函数时触发ORA-00936错误，函数代码如下：

FUNCTION isConditionsForChargeChanged(
                                      adj_date varchar2, 
                                      acc_num  varchar2
                                     ) RETURN integer IS
  res integer;
BEGIN
    SELECT CASE
             WHEN EXISTS
                 ( SELECT DISTINCT 1
                     FROM custhasproduct@rbm_dblink chp
                     JOIN custproductdetails@rbm_dblink cpd
                       ON chp.customer_ref = cpd.customer_ref
                      AND chp.product_seq = cpd.product_seq
                     JOIN custproducttariffdetails@rbm_dblink cptd
                       ON cptd.customer_ref = cpd.customer_ref
                      AND cptd.product_seq = cpd.product_seq
                     JOIN custproductstatus@rbm_dblink cps
                       ON cps.customer_ref = chp.customer_ref
                      AND cps.product_seq = chp.product_seq
                     JOIN tariff@rbm_dblink tr
                       ON tr.tariff_id = cptd.tariff_id
                      AND tr.catalogue_change_id in
                          (SELECT catalogue_change_id
                             FROM cataloguechange
                            where catalogue_status = 3)
                     JOIN custoverrideprice@rbm_dblink cop
                       ON cop.customer_ref = cpd.customer_ref
                      AND cop.product_seq = cpd.product_seq
                    WHERE cpd.account_num = acc_num
                      AND ((EXTRACT(month FROM cps.effective_dtm) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cps.effective_dtm) =
                          EXTRACT(year FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          cps.product_status in ('SU', 'OK', 'TX')) /* Проверка изменения активности услуги в данном отчетном периоде */
                          OR (EXTRACT(month FROM cptd.start_dat) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cptd.start_dat) =
                          EXTRACT(year FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          cptd.END_DAT IS NULL) /* Проверка на смену тарифа в данном отчетном периоде*/
                          OR (EXTRACT(month FROM cop.start_dat) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cop.start_dat) =
                          EXTRACT(year FROM date
                                       TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          COP.END_DAT IS NULL)) /* Проверка на переопределение цены в данном отчетном периоде */
                 ) THEN
              0
             ELSE
              1
           END
      INTO res
      FROM dual;

  RETURN res;
END isConditionsForChargeChanged;

执行时触发以下错误：

PL/SQL: ORA-00936: Missing expression Line 190 Pos 46
PL/SQL: SQL Statement ignored Line 181 Pos 3

报错位置标注：

...and ((extract (month from cps.effective_dtm) (?)= extract (month from to_date(adj_date, 'YYYY-MM-DD'))...
...(?)SELECT CASE when EXISTS (...

错误原因 #

错误根源在于第三个OR条件中的语法冗余：EXTRACT(year FROM date TO_DATE(adj_date, 'YYYY-MM-DD')) 里多了一个多余的date关键字。TO_DATE(adj_date, 'YYYY-MM-DD')本身已经是合法的日期表达式，前面添加date会导致SQL解析器无法识别有效表达式，从而触发ORA-00936错误。

修正后的代码 #

将多余的date关键字删除即可，修正后的函数代码如下：

FUNCTION isConditionsForChargeChanged(
                                      adj_date varchar2, 
                                      acc_num  varchar2
                                     ) RETURN integer IS
  res integer;
BEGIN
    SELECT CASE
             WHEN EXISTS
                 ( SELECT DISTINCT 1
                     FROM custhasproduct@rbm_dblink chp
                     JOIN custproductdetails@rbm_dblink cpd
                       ON chp.customer_ref = cpd.customer_ref
                      AND chp.product_seq = cpd.product_seq
                     JOIN custproducttariffdetails@rbm_dblink cptd
                       ON cptd.customer_ref = cpd.customer_ref
                      AND cptd.product_seq = cpd.product_seq
                     JOIN custproductstatus@rbm_dblink cps
                       ON cps.customer_ref = chp.customer_ref
                      AND cps.product_seq = chp.product_seq
                     JOIN tariff@rbm_dblink tr
                       ON tr.tariff_id = cptd.tariff_id
                      AND tr.catalogue_change_id in
                          (SELECT catalogue_change_id
                             FROM cataloguechange
                            where catalogue_status = 3)
                     JOIN custoverrideprice@rbm_dblink cop
                       ON cop.customer_ref = cpd.customer_ref
                      AND cop.product_seq = cpd.product_seq
                    WHERE cpd.account_num = acc_num
                      AND ((EXTRACT(month FROM cps.effective_dtm) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cps.effective_dtm) =
                          EXTRACT(year FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          cps.product_status in ('SU', 'OK', 'TX')) /* Проверка изменения активности услуги в данном отчетном периоде */
                          OR (EXTRACT(month FROM cptd.start_dat) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cptd.start_dat) =
                          EXTRACT(year FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          cptd.END_DAT IS NULL) /* Проверка на смену тарифа в данном отчетном периоде*/
                          OR (EXTRACT(month FROM cop.start_dat) =
                          EXTRACT(month FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          EXTRACT(year FROM cop.start_dat) =
                          EXTRACT(year FROM TO_DATE(adj_date, 'YYYY-MM-DD')) AND
                          COP.END_DAT IS NULL)) /* Проверка на переопределение цены в данном отчетном периоде */
                 ) THEN
              0
             ELSE
              1
           END
      INTO res
      FROM dual;

  RETURN res;
END isConditionsForChargeChanged;

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