|
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
|
|
|
|
|
Sander Rossel wrote: If it looks stupid and it works then it ain't stupid
Sander Rossel wrote: 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
My answer actually wouldn't be Oracle specific.
|
|
|
|
|
Jörgen Andersson wrote: My answer actually wouldn't be Oracle specific.
In that case I'm interested
|
|
|
|
|
Sorry for not replying yet, but the scope grew out of proportion.
This will either turn into an article, or I'll need to start over from the beginning.
|
|
|
|
|
No problem, looking forward to it
|
|
|
|
|
Sporting gear, flyer perches on crude sphere (8, 3)
Slogans aren't solutions.
|
|
|
|
|
Sporting gear,
flyer BAT
perches on
crude BASE
sphere BALL
BASEBALL BAT
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Ah The Beloved Weapon of Millwall fans visiting West Ham 
We can’t stop here, this is bat country - Hunter S Thompson RIP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
