技术标签:

【中文标题】由于 ORA-00920 导致过程失败：无效的关系运算符【英文标题】：Procedure failed due to ORA-00920: invalid relational operator 【发布时间】：2015-11-13 07:23:44 【问题描述】：

在 12c oracle 中执行时出现以下错误，但在 oracle 10.2.0.4 中运行正常：

由于 ORA-00920 导致过程 proc_up_dts_product_cat_dynsql 失败：无效关系运算符这是程序：

CREATE OR REPLACE PROCEDURE SEAPROB.proc_up_dts_product_cat_dynsql(tablename IN varchar)ASdynamicsql varchar(8000);ID int;DTS_Segment_op  varchar2(10);DTS_Segment varchar2(15);DTS_Segment_where   varchar2(255);DateEntered_op  varchar2(10);DateEntered varchar2(30);DateEntered_where   varchar2(255);Svc_Name_op varchar2(10);Svc_Name    varchar2(100);Svc_Name_where  varchar2(255);Product_Category    varchar2(75);Priority    int;combined_status_where varchar2(255);refdate date ;CURSOR PrivCursorISSELECTID,DTS_Segment_op,Nvl(upper(trim(DTS_Segment))," "),DateEntered_op,CASE WHEN dateentered="%" THEN dateentered  WHEN dateentered LIKE "%/%/____" THEN To_Char(To_Date(dateentered,"MM/DD/YYYY"),"YYYY-MM-DD")  WHEN dateentered LIKE "%/%/__" THEN To_Char(To_Date(dateentered,"MM/DD/YY"),"YYYY-MM-DD")  WHEN dateentered LIKE """%/%/%"" % ""%/%/%""" THEN """"||To_Char(To_Date(SubStr(dateentered,InStr(dateentered,"""",1,1)+1,InStr(dateentered,"""",1,2)-InStr(dateentered,"""",1,1)-1),"MM/DD/YY"),"YYYY-MM-DD")||""" and """||To_Char(To_Date(SubStr(dateentered,InStr(dateentered,"""",1,3)+1,InStr(dateentered,"""",1,4)-InStr(dateentered,"""",1,3)-1),"MM/DD/YY"),"YYYY-MM-DD")||""""  ELSE dateentered END AS dateentered,Svc_Name_op,Nvl(upper(trim(Svc_Name))," "),Product_Category,PriorityFROM tbl_dts_pt_lookup order by priority desc;BEGINrefdate := ADD_MONTHS(to_date(SYSDATE,"dd-mon-yy"),-6) ;OPEN PrivCursor;-- Loop through all the rows in the tbl_dts_category_lookup tableFETCH PrivCursorINTOID,DTS_Segment_op,DTS_Segment,DateEntered_op,DateEntered,Svc_Name_op,Svc_Name,Product_Category,Priority;WHILE PrivCursor%foundLOOP-- Create dynamic SQL--define case statements for where clause componentscombined_status_where := " where (DTS_Cm_DisputeStatus <>"|| """C""" || " OR ( DTS_Cm_DisputeStatus="|| """C""" ||  " AND DTS_CM_CLOSEDATE >=  """||refdate||"""))" ;dts_segment_where := case when dts_segment="%" then " and 1=1" else " and NVL(trim(Replace(Upper(segment),chr(0),""" || """)),""" || " "") " || dts_segment_op || " """ || dts_segment || """" end;svc_name_where := case when svc_name="%" then " and 1=1" else " and NVL(trim(Replace(Upper(dts_cm_servicename),chr(0),""" || """)),""" || " "") " || svc_name_op || " """ || svc_name || """" end ;dateentered_where := case when dateentered="%" then " and 1=1"                                 when dateentered_op="between" then " and TO_CHAR(dts_cm_dateentered,""YYYY-MM-DD"") between " || dateentered                                 else " and TO_CHAR(dts_cm_dateentered,""YYYY-MM-DD"") " || dateentered_op || " """ || dateentered || """" end ;dynamicsql := "update "||tablename||" set product_cat_, product_category =""" || product_category || """";--add where clausedynamicsql := dynamicsql || combined_status_where || dts_segment_where || dateentered_where || svc_name_where;EXECUTE IMMEDIATE dynamicsql;COMMIT;FETCH PrivCursorINTOID,DTS_Segment_op,DTS_Segment,DateEntered_op,DateEntered,Svc_Name_op,Svc_Name,Product_Category,Priority;END LOOP;CLOSE PrivCursor;EXCEPTION   WHEN OTHERS THEN   DBMS_OUTPUT.PUT_LINE("Procedure proc_up_dts_product_cat_dynsql failed due to "||SQLERRM);END proc_up_dts_product_cat_dynsql;/

【问题讨论】：

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE 首先，从您的代码中删除此错误。然后使用SHOW ERRORS 获取具有确切行号的完整错误堆栈。如果是运行时错误，则复制粘贴您的 SQL*Plus 会话并编辑您的问题以添加详细信息。 @LalitKumarB 为什么认为EXCEPTION WHEN OTHERS THEN.. 是一个错误？ @Moudiz 只需谷歌WHEN OTHERS Oracle bug。否则，请阅读我的文章，WHEN OTHERS – A bug 其他一些有用的文章 orafaq.com/wiki/WHEN_OTHERS 和 Thomas Kyte 的这篇 tkyte.blogspot.in/2012/05/pokemon-and-when-others.html 以及 asktom.oracle.com/pls/apex/… 底线“当其他人几乎总是BUG，除非紧跟在 RAISE 之后。" @Bhuvananagulan 您能否评论EXECUTE IMMEDIATE 语句并在其前面添加DBMS_OUTPUT.PUT_LINE(dynamicsql); 并在SQL*Plus 中重新执行。请确保在执行该过程之前执行set serveroutput on。 尝试在没有 DBMS_OUTPUT 的情况下执行在下面的行中出现错误：dynamicsql := "update "||tablename||"设置 product_cat_, product_category =""" ||产品类别 || """"; --add where 子句 dynamicsql := dynamicsql ||组合状态位置 || dts_segment_where || dateentered_where || svc_name_where; 【参考方案1】：

当我们在 WHERE 子句中提交带有语法错误的 SQL 语句时，数据库会抛出 ORA-00920: invalid relational operator。通常这是一个错字。这在动态 SQL 中非常容易做到，因为除了运行时我们无法看到整个语句。

您尚未发布生成的更新声明。好吧，您不能，因为您的异常处理不会显示它。专业提示：使用动态 SQL 时始终记录或显示生成的语句，如果无处可在异常块中：

dbms_output.put_line("generated statement:"|| dynamicsql);

所以这只是一个猜测，但这条线看起来很可疑......

 when dateentered_op="between" then " and TO_CHAR(dts_cm_dateentered,""YYYY-MM-DD"") between " || dateentered

... 因为它似乎生成了一个没有 AND 子句的 BETWEEN 语句。 BETWEEN 运算符需要两个界限。

但这可能是很多事情。您可能正在使用不寻常的参数组合运行，这会生成无效的语句。您需要更好的日志记录。

【讨论】：

以上是关于由于 ORA-00920 导致过程失败：无效的关系运算符的主要内容，如果未能解决你的问题，请参考以下文章