不一致的数据类型:期望%s得到%s;用于Oracle CONNECT BY NOCYCLE函数

Question

我试图找到我的层次结构中的循环。我有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:

======================如果你观察到我已经将输入参数显式转换为CHAR，但仍然存在错误。

似乎存在错误

TO_CHAR (s_rowid_party_parent_fk) = TO_CHAR (s_rowid_party_child_fk))

感谢您的帮助。

感谢

即使尝试了也没有运气

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;

Answer 1

不确定您是否正确地将这些* _fk转换为字符数据类型，以及它们仍然存在什么。你能展示C_REL_PARTY_XREF表的定义吗?无论如何，如果这些列都是行，请尝试以下操作…

我认为你的比较可能有问题，尝试使用ROWIDTONCHAR函数。所以改成这段代码:

TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk)

使用

:

ROWIDTONCHAR(s_rowid_party_parent_fk)=ROWIDTONCHAR(s_rowid_party_child_fk)

但完全正确的应该是这样的(正如Mihain在评论中指出的):

NVL(ROWIDTONCHAR(s_rowid_party_parent_fk), '#')=NVL(ROWIDTONCHAR(s_rowid_party_child_fk), '#')