title: Inconsistent datatypes: expected %s got %s ; for Oracle CONNECT BY NOCYCLE function
body: <p>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.</p>
  
  <p>===========================================</p>
  
  <pre><code>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&lt;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 ---&gt;' || 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:
  </code></pre>
  
  <p>======================
  if you observer I have converted input parameter explicitly to CHAR but still error exist .
  looks like error exist at </p>
  
  <p>TO_CHAR(s_rowid_party_parent_fk)=TO_CHAR(s_rowid_party_child_fk))</p>
  
  <p>any help appreciated .</p>
  
  <p>Thanks</p>
  
  <p>EVEN TRIED  BUT NOT Luck</p>
  
  <pre><code>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;
  </code></pre>