Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchial query - CONNECT_BY_LOOP (Oracle 11g R2)
Hierarchial query - CONNECT_BY_LOOP [message #594993] Wed, 04 September 2013 12:09 Go to next message
nvembu
Messages: 4
Registered: July 2013
Junior Member
I have a hierarchial query within a package that fails with the below error

ORA-01436: CONNECT BY loop in user data

I am able to fix the package and run the query using NOCYCLE clause, but I want to identify the record causing the LOOP issue.

I used the below query with CONNECT_BY_ISCYCLE to help identigy it, but the table has a very large amount of data and even though the query returns results,
I am not able to export it or view more than some n rows, to check out or validate the row with CONNECT_BY_ISCYCLE = 1.

When I try to export it, my console(I use SQL developer) hangs after a certain time. I tried creating a temp table of sorts to insert the dataset of the query. Same result.

Is there any way to modify the query to return just teh issue record or any easy way to retrieve the dataset returned by the query?

select a,b,connect_by_iscycle iscycle
from t1
where startdate > '15-Jun-2013'
and enddate < '21-Jun-2013'
start with a = 123
connect by nocycle prior a = b;

Cheers,
vem
Re: Hierarchial query - CONNECT_BY_LOOP [message #594995 is a reply to message #594993] Wed, 04 September 2013 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
since we don't have your tables or data, we can not run, test, debug, or improve posted code.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

if START_DATE & END_DATE are really DATE datatype then they should NOT be compared to strings.
Re: Hierarchial query - CONNECT_BY_LOOP [message #594996 is a reply to message #594993] Wed, 04 September 2013 12:22 Go to previous message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

'15-Jun-2013' is a STRING not a DATE, the proof:
SQL> select to_date('15-Jun-2013') from dual;
select to_date('15-Jun-2013') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Regards
Michel
Previous Topic: How to Select data when it has a single quote in the value.
Next Topic: Get Total Sales Dollar Amount from two tables without cursor
Goto Forum:
  


Current Time: Thu Nov 27 16:01:35 CST 2014

Total time taken to generate the page: 0.13318 seconds