|
Unless someone knows the problem, in that case it becomes a question.
So I'm trying a little CONNECT BY PRIOR, works like a charm.
Now I need to access the root, so I found CONNECT_BY_ROOT, which also works pretty well.
Except, when I put CONNECT_BY_ROOT in a JOIN or WHERE clause, something that's apparently not allowed, I get a generic ORA-600 error and Oracle closes my connection.
According to orafaq:
ORA-600 is an internal error generated by the generic kernel code of the Oracle RDBMS software. It is different from other Oracle errors in many ways. The following is a list of these differences:
1. An ORA-600 error may or may not be displayed on the screen. Therefore, screen output should not be relied on for capturing information on this error. Information on ORA-600 errors are found in the database alert and trace files. We recommend that you check these files frequently for database errors. (See the Alert and Trace Files section for more information.)
2. Each ORA-600 error comes with a list of arguments They usually enclosed in square brackets and follow the error on the same line for example:
3. Also some time, if we have used many cte's in a SP then it throws same error because of memory used by these ctc's exceed.
HOW THE HELL DOES AN INVALID (PL/)SQL STATEMENT THROW AN ERROR IN THE KERNEL INSTEAD OF SHOWING A PROPER ERROR MESSAGE!?     
|
|
|
|
|
Sander Rossel wrote: HOW THE HELL DOES AN INVALID (PL/)SQL STATEMENT THROW AN ERROR IN THE KERNEL INSTEAD OF SHOWING A PROPER ERROR MESSAGE!?
Simple: bad programming.
Was Oracle written by asking questions in QA? 
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
No, but I think it was designed by committee.
Cheers,
Mick
------------------------------------------------
It doesn't matter how often or hard you fall on your arse, eventually you'll roll over and land on your feet.
|
|
|
|
|
Would that be the same people who designed the Fiat Multipla?
Clarkson[^] described it as "designed by a group of people who seemingly never met" - which fits what little I know of Oracle well.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Similar sort of animal, I would surmise. A committee is a bunch of people that enjoy having meetings, and an old adage I find to be painfully true is
If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'.
Cheers,
Mick
------------------------------------------------
It doesn't matter how often or hard you fall on your arse, eventually you'll roll over and land on your feet.
|
|
|
|
|
Can't remember who said it first: "The IQ of a committee is the IQ of the dimmest person there, divided by the number of members"
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
OriginalGriff wrote: Was Oracle written by asking questions in QA?
There's a mental facility outside of Orlando that I think had something to do with it.
New version: WinHeist Version 2.2.2 Beta I told my psychiatrist that I was hearing voices in my head. He said you don't have a psychiatrist!
|
|
|
|
|
OriginalGriff wrote: Was Oracle written by asking questions in QA?
Don't think, but over 85% of Oracle employees are lawyers...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Getting ready to be very busy in 2017...
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
OriginalGriff wrote: Was Oracle written by asking questions in QA? 
I'm starting to think so.
And then they made it the most expensive database out there and for some reason people bought it... 
|
|
|
|
|
You are obviously hitting a bug in the internal Oracle DB code, as you have already stated, more or less.
I would recommend rewriting your code/query a bit and testing for a more positive result. Check the trace files for more info on what may be causing this error.
Welcome to the hideous world of Oracle.
ORA-600 Troubleshooting (Oracle DB/EM Support)[^]
|
|
|
|
|
Slacker007 wrote: Check the trace files for more info on what may be causing this error.
I've fixed it using a loop, much much much faster in my case. I can't access the server directly, I don't know how to get it using SQL Developer and so I'd have to ask around and then learn how to use/read trace files.
I only use Oracle for this specific customer, otherwise I use SQL Server.
I've heard so much about Oracle, it was supposed to be the RDBMS heaven, justifying its expensive price.
So far it's done nothing than disappoint
Only looping is much easier in Oracle 
|
|
|
|
|
I worked with Oracle exclusively for over a year (about 2 years ago), and I was very disappointed with the product as well.
Good for you for finding a workaround/solution. 
|
|
|
|
|
I've had errors like that in the past doing XML manipulation -- the SQL statement parser can get very lost, confused, and messy, and in particular, I experienced getting different (wrong) results simply by changing the order of select columns / where clauses -- keep in mind this was all related to XML queries.
Anyways, does this[^] help? See example lower down for CONNECT BY ROOT.
Marc
|
|
|
|
|
It works fine in the SELECT clause, apparently it works in ways that don't allow for it to be used in JOINS or WHERE clauses.
I've tried it with other queries that did not crash and disconnect me, but I still got the error that I needed a CONNECT BY PRIOR clause while I most certainly had that.
|
|
|
|
|
Oracle is the perfect tool for when a fully functioning RDBMS would be too affordable.
Slogans aren't solutions.
|
|
|
|
|
|
As already said, you're most likely experiencing a bug in which case the arguments of ora-600 would be interesting.
But as a quick fix, if you need recursion, why not use CTE (WITH clause)?
|
|
|
|
|
Unfortunately, our Oracle version is too old, 10g 
|
|
|
|
|
|
Mika Wendelius wrote: You do know that regular support has ended 2010 and even extended support ended 2013
Nope, but I'm not the one making those decisions. I'm just being told "you need to work for that customer now and that's their database".
I usually work in SQL Server 
|
|
|
|
|
Without seeing your actual query I can't say for sure, but connect by prior and joins doesn't cooperate well. That's quite well documented by the way. And fixed in later versions as well. Certain constructs are still not allowed, but then you'll get a proper error message.
A very simple workaround is usually to put the join inside a CTE.
Regarding the performance, you should always add two composite indexes on the table you're running CONNECT BY PRIOR on. One on (Id,ParentId) and one on (ParentId,Id).
Secondly, you should keep in mind that Connect by Prior does not behave like it's set based. You can compare it with a recursive CTE depth first instead of breadth first. So in this case the power comes at the cost of speed.
But what a power it is! Luckily I don't work with hierarchies anymore, as SQLServer sucks really badly at it.
Yes I know about the HierarchyID, and how someone could be allowed to implement a path enumeration model instead of an adjacency list or a nested set model, without getting flogged, is beyond my comprehension.
|
|
|
|
|
Jörgen Andersson wrote: A very simple workaround is usually to put the join inside a CTE.
That's my default as I'm really a SQL Server guy which only knows that syntax. Unfortunately, our Oracle version is old and doesn't support it yet.
The deepest hierarchy we have is about five levels deep. On a small data set, so performance is not much of a problem.
Jörgen Andersson wrote: Luckily I don't work with hierarchies anymore, as SQLServer sucks really badly at it.
So what do you do when you have hierarchies?
|
|
|
|
|
I'm not talking about recursive CTE's. I mean that you should put the joined tables inside a CTE, and run the CONNECT BY PRIOR using the CTE as source, alternately put the CONNECT BY PRIOR inside a CTE and then join using this CTE as a source table. Whatever fits the requirements the best.
Sander Rossel wrote: So what do you do when you have hierarchies?
Depends on size, whether it's fairly static or have frequent insertions, what kind of queries you need to do (subtrees, paths, lowest common ancestor)...
I have even used the path enumeration model since it's fairly humanly readable. It's just so prone to f***ups.
I'll expand the answer tonight if you want me to, but first I need to get back from town.
|
|
|
|
|
Jörgen Andersson wrote: I mean that you should put the joined tables inside a CTE, and run the CONNECT BY PRIOR using the CTE as source
That's what I'm doing now. Use a CTE for the CONNECT BY PRIOR, get additional data by joining on the CTE and then looping through the records.
If it looks stupid and it works then it ain't stupid 
Jörgen Andersson wrote: I'll expand the answer tonight if you want me to, but first I need to get back from town.
Thanks, but don't trouble yourself. My vacation starts tomorrow and I won't be doing a lot of Oracle development in the new year anyway
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
