不一致的数据类型：预期 %s 得到 %s ；对于 Oracle CONNECT BY NOCYCLE 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25340892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inconsistent datatypes: expected %s got %s ; for Oracle CONNECT BY NOCYCLE function
提问by Yuva Raj
Am trying to find the loops in my hierarchies. I have 250k+ which defines Hierarchies. I have created below procedure to print loop in hierarchies we have fix them.
我试图在我的层次结构中找到循环。我有 250k+ 定义层次结构。我创建了以下程序来在我们修复它们的层次结构中打印循环。
===========================================
============================================
DECLARE
STMT1 VARCHAR2(5000);
RESULT1 VARCHAR2(3000);
CHILDREN C_REL_PARTY_XREF.S_ROWID_PARTY_CHILD_FK%type;
CURSOR C1 IS
SELECT DISTINCT S_ROWID_PARTY_CHILD_FK
FROM C_REL_PARTY_XREF A
, C_REL_PARTY B
WHERE B.ROWID_OBJECT = A.ROWID_OBJECT
AND B.HUB_STATE_IND =1
AND ROWNUM<50;
--238 K CHILD'S
BEGIN
FOR FIND_CHILD IN C1
LOOP
--CHILDREN := TO_CHAR('''' ||FIND_CHILD.S_ROWID_PARTY_CHILD_FK|| '''');
CHILDREN :=FIND_CHILD.S_ROWID_PARTY_CHILD_FK;
*/find the loop*/
STMT1:='WITH RECORDS AS (
SELECT LEVEL, s_rowid_party_child_fk , s_rowid_party_parent_fk ,CONNECT_BY_ISCYCLE AS TRIPPED
FROM C_REL_PARTY_XREF
START WITH s_rowid_party_child_fk ='||CHILDREN||'
CONNECT BY NOCYCLE PRIOR
TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk))
SELECT * FROM RECORDS
WHERE TRIPPED = 1 ';
EXECUTE IMMEDIATE STMT1 INTO RESULT1;
/* print loop */
IF (RESULT1 IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE( 'LOOP HAPPENING AT --->' || RESULT1);
END IF ;
END LOOP ;
=======================
am getting error as below */this is the error is printed.
Error report:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 34
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
====================== if you observer I have converted input parameter explicitly to CHAR but still error exist . looks like error exist at
====================== 如果您观察我已将输入参数显式转换为 CHAR 但仍然存在错误。看起来错误存在于
TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk))
TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk))
any help appreciated .
任何帮助表示赞赏。
Thanks
谢谢
EVEN TRIED BUT NOT Luck
甚至尝试过但不是运气
SET SERVEROUTPUT ON ;
DECLARE
STMT1 VARCHAR2(2000);
RESULT1 VARCHAR2(2000);
BEGIN
STMT1:= '
SELECT LEVEL, s_rowid_party_child_fk , s_rowid_party_parent_fk ,CONNECT_BY_ISCYCLE AS TRIPPED
FROM C_REL_PARTY_XREF
START WITH s_rowid_party_child_fk =''1749''
CONNECT BY NOCYCLE PRIOR s_rowid_party_parent_fk=s_rowid_party_child_fk
';
EXECUTE IMMEDIATE STMT1 INTO RESULT1;
DBMS_OUTPUT.PUT_LINE(STMT1);
DBMS_OUTPUT.PUT_LINE(RESULT1);
END;
回答by WojtusJ
Not sure if you are correctly converting those *_FKs to character datatype, and what still lies in them. Can you show the definition of C_REL_PARTY_XREF table? Anyway, if those columns are rowids anyway, try the below...
不确定您是否正确地将这些 *_FK 转换为字符数据类型，以及它们中仍然存在的内容。你能显示 C_REL_PARTY_XREF 表的定义吗？无论如何，如果这些列无论如何都是 rowids，请尝试以下...
I think there might be a problem with your comparison, try to use ROWIDTONCHARfunction. So instead this code:
我认为您的比较可能有问题，请尝试使用ROWIDTONCHAR函数。所以改为这个代码：
TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk)
use that:
使用：
ROWIDTONCHAR(s_rowid_party_parent_fk)=ROWIDTONCHAR(s_rowid_party_child_fk)
but totally correct would be something like that (as pointed out Mihain in the comments):
但完全正确的应该是这样的（正如 Mihain 在评论中指出的那样）：
NVL(ROWIDTONCHAR(s_rowid_party_parent_fk), '#')=NVL(ROWIDTONCHAR(s_rowid_party_child_fk), '#')
