'Inconsistent datatypes: expected %s got %s ; for Oracle CONNECT BY NOCYCLE function

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.

===========================================

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

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;
oraclefor-loophierarchical-data


Solution 1:[1]

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...

I think there might be a problem with your comparison, try to use ROWIDTONCHAR function. So instead this code:

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):

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 WojtusJ

Related Questions