ORA-01436:CONNECT BY loop in user data

So, what is the problem?
I got an error ORA 01436: CONNECT BY LOOP in user data.
Hmm..What did you do to get this error?
Nothing. I was just running a hierarchical query in Oracle and somehow ended up with this error. First, I don’t really know what this error means to me and secondly, I don’t know how it came in my query.
Do you have the Query and the scripts to create and insert data into the table?
Sorry, I don’t have that with me right now.
Ok. What do you want me do to for you now?
Three things:
  1. What does the error code mean?
  2. How to simulate the error?
  3. How to debug when we get this error?
Ok. By the way, what version of Oracle you are using?
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
Right, here we go. ‘What does the error code mean?’
Oracle throws this error when you try to use a ‘CONNECT BY’ constructs in a query and it results in a loop. By ‘loop’, what is conveyed is, the query results at some point in time during execution starts to return back to the point from where it started ad infinitum. During such instances, Oracle detects the error and to avoid the looping, gives a meaningful message on the screen like the one shown below
Aaha...Now I know. But, Can you simulate this error with a simple example for me?
Yes. Here we go. Let us create a simple table with two fields:
CREATE TABLE CONNECT_BY_LOOP
(ID1
NUMBER,
ID2
NUMBER)
Now, I try to insert exactly two rows into this table. The ‘insert’ statement is provided below
INSERT INTO connect_by_loop VALUES ('1','2')INSERT INTO connect_by_loop VALUES ('2','1')
COMMIT the transaction.
Now, if you give, “select * from connect_by_loop” this is what you see
And here is the problematic SQL that will simulate this error
SELECT * FROM connect_by_loopSTART WITH id1='1'
CONNECT BY PRIOR id1=id2
What happens here is Oracle retrieves the first row and then uses‘1’ as ID2 and retrieves the second row. Now, it tries to use ‘2’ as ID2 because of the PRIOR condition and detects a loop there. It throws this error right on the screen that you saw earlier.
Can I force Oracle to return a row in such cases?
Yes, you can. Use the ‘NOCYCLE’ parameter in the SQL and you are done! This is how the SQL looks like
SELECT id1,id2 FROM connect_by_loopSTART WITH id1='1'
CONNECT BY NOCYCLE PRIOR id1=id2
That’s cool. But if I have a big table, how can I find out which row is creating the problem? It would have been so nice if Oracle has provided a shortcut route for that.
Fortunately, for your database version yes. You don’t have to toil that hard to find the row that is causing the loop. Here is a neat SQL that will help in identifying it.
SELECT id1,id2,CONNECT_BY_ISCYCLE "loop" FROM connect_by_loopSTART WITH id1='1'
CONNECT BY NOCYCLE PRIOR id1=id2
Notice the keyword “CONNECT_BY_ISCYCLE”. The output looks likes this
In row # 2, you see a value of ‘1’ for loop. So, this is the row, which is causing the ‘cyclic’ effect. If this row is fixed, then the problem is resolved.
That is really great! This saves a lot of my time in identifying the problematic row. Oracle is great..
Yes, in fact it is. Now, go home and find the row and fix it. See your hierarchical queries in action. Have a good time!




Sri Lanka v India, 1st Test, Watch Live

Some of the links that I collected to watch the first test between India and Srilanka live from SSC, Colombo. Check these out and this page for updates.

Link 1

Stanley Kamel - Best Collection

Check out these nice collection of Stanley Kamel that I found in You Tube. Must watch Videos..

IN MEMORIAM STANLEY KAMEL

Stanley Kamel in Knight Rider

Stanley Kamel in Domino

Stanley Kamel in Diagnosis Murder

England Vs South Africa 1st Test - Watch Live

Some of the link that I found in the web to watch the 1st test cricket match between England and South Africa Live. Check out these links.

Link 1

Link 2

Link 3

Interpreting Modern Art

Somebody gave me this picture stating that this is a modern art. I'm trying to figure out what does this mean? Is this really a modern art? If so, what is the meaning conveyed?

Interpreting Modern Art
Add caption

ISTQB Model Question Paper

ISTQB Advanced Test Analyst Exam Preparation Guide

Here is a neat link that I found in Scribd towards ISTQB certification. Check out this Model paper.


Read this document on Scribd: ISTQB Mock Papers

Marc Chagall

One of the greatest painters - Marc chagall..Today is the birthday of Marc Chagall

I was searching for his Popular paintings... Found this link on youtube. You can watch the paintings done by Marc chagall as a slide show.


Asia Cup 2008 Finals Highlights

Highlights of the Asia Cup 2008 final match played between India and Srilanka.

Srilankan Innings

Jayasuraya Shots!

Indian Innings

Mendis spell

Asia Cup 2008 Finals - Watch Live

Some of the links that I found in the web to watch Asia Cup 2008 finals live.

Link 1

Link 2

Wimbledon 2008 Finals - Highlights

Presentation during the Women's singles final at Wimbledon 2008. Check here.

Ist set break point. Click here.

Wimbledon doubles won by Williams Sisters by defeating Lisa Raymond and Samantha Stosur, 6-2, 6-2.

Remove antivirus 2008 spyware

Well, it said "Your computer is infected. Click to install and clean now". That too with a nice image, tempting my little brother to do a click. He was so happy after that, called me and told.."I installed a nice antivirus tool to remove viruses..that too for free". Great! what is that tool..I was wondering..He replied back telling "Antivirus 2008".....My boy! what have you done.! Do you think it is an ANTIVIRUS software? Not at all! It is a malware in itself!

So, how to go ahead and remove it...? Let us learn something about this Antivirus 2008 before trying to remove it..

Antivirus 2008 is a rogue anti-malware software that displays fake results and requires you to purchase the software. Antivirus2008 will scan your computer and list a variety of infections found on your computer. It also has the capability to download and install further infections to your PC. It will open the doorway to a lot of other infections..


Some sites instruct the user to delete the files

AntiVirus 2008.lnk
Uninstall.lnk
AntiVirus 2008.exe
AntiVirus 2008.lic
AntiVirus 2008.dll
Uninstall.exe

The worst part it the user will not be allowed to delete the files..It copies itself so quickly that it will be really difficult to delete the files..

I would recommend going for Spyware Doctor to remove this. The most trusted antispyware software. It really cleans and remove all the traces of this spyware. It also helped me in removing virtumonde. A one time investment that will always make sure your PC is safe and secure.

Also, do not get misled by some rogue spyware removal softwares, pretending to clean this one. They are brothers of this malware....

Asia Cup 2008 India Vs Pakistan Highlights

Highlights of the super four match in the Asia cup 2008, between India and Pakistan. I found some links in the web. Here are they..

India Innings.

Pakistan Innings.

Rohit Sharma Fifty