oracle11g - Getting error as "ORA-32044: cycle detected while executing recursive WITH query" -

oracle11g - Getting error as "ORA-32044: cycle detected while executing recursive WITH query" -

i getting error "ora-32044: cycle detected while executing recursive query" while executing next query in oracle.

with empscte (affiliation_id, from_customer_id,to_customer_id, to_name, level1) ( select affiliation_id, from_customer_id,to_customer_id, to_name, 0 affiliation aff to_customer_id != from_customer_id , to_customer_id = 1000022560394 union select aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1 affiliation aff inner bring together empscte m on aff.to_customer_id = m.from_customer_id ) select * empscte;

your code working fine except 1 info status when to_customer (1000022560394) himself have started transaction in first place , after level of transaction beingness returned him only.

eg- sample info set

for case, recursive part of query find conditions true @ end of transaction, info there both in normal table , incremental dataset.

one solution create match-flag determine number of encounter , avoid infinite loop:

with empscte (affiliation_id, from_customer_id,to_customer_id, to_name,level1,match_count) ( select affiliation_id, from_customer_id,to_customer_id, to_name, 0, 0 match_count affiliation aff to_customer_id != from_customer_id , to_customer_id = 1000022560394 union select aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1,1 match_count affiliation aff inner bring together empscte m on aff.to_customer_id = m.from_customer_id m.match_count=0 ) select * empscte;

oracle11g