Home » SQL & PL/SQL » SQL & PL/SQL » ora-00600 during select query execution... (oracle 9.2.0.8)
ora-00600 during select query execution... [message #316320] Thu, 24 April 2008 11:00 Go to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Hi,

Could you please let me resolve this ora-00600 error { "ORA-00600: internal error code, arguments: [qkajoi:1], [], [], [], [], [], [], []" } which occurs when i execute a select statement. The statement is pasted below for your reference:

SELECT PATH AS USER_ID, USER_NM, LEVEL
FROM
(
WITH PARENT_QUERY AS
(
SELECT a.USER_ID, a.USER_ID AS PATH, B.USER_NM, 1 AS MAX_LEVEL,NULL PARENT_USER_ID FROM GSS_USER_RETAILER_MAP A, GSS_USER B
WHERE A.USER_ID = B.USER_ID AND A.RETAILER_ID= 'RMAKHOSO' AND B.GMAC_DEALER_USER_FLG = 'D' AND B.STATUS='ACTIVE'
AND EXISTS (SELECT C.PARENT_USER_ID FROM GSS_USER_HIERARCHY C WHERE C.PARENT_USER_ID=B.USER_ID
START WITH C.PARENT_USER_ID = 'MULTIDEALER1' CONNECT BY PRIOR C.USER_ID = C.PARENT_USER_ID)
AND NOT EXISTS (SELECT D.USER_ID FROM GSS_USER_HIERARCHY D WHERE D.USER_ID=B.USER_ID START WITH PARENT_USER_ID = 'MULTIDEALER1' CONNECT BY PRIOR USER_ID = PARENT_USER_ID)
UNION
SELECT E.USER_ID , E.USER_ID AS PATH, E.USER_NM, 1 AS MAX_LEVEL,NULL PARENT_USER_ID
FROM GSS_USER E , GSS_USER_RETAILER_MAP F WHERE E.USER_ID=F.USER_ID
AND F.RETAILER_ID= 'RMAKHOSO' AND E.GMAC_DEALER_USER_FLG = 'D' AND E.STATUS='ACTIVE' AND E.USER_ID= 'MULTIDEALER1'
)
SELECT USER_ID, PATH, USER_NM, MAX_LEVEL, PARENT_USER_ID FROM PARENT_QUERY
UNION
SELECT A.USER_ID, LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(A.USER_ID,'/') AS PATH, B.USER_NM, LEVEL+1 AS MAX_LEVEL, A.PARENT_USER_ID
FROM GSS_USER B, GSS_USER_HIERARCHY A WHERE B.USER_ID=A.USER_ID AND UPPER(B.STATUS) = 'ACTIVE' AND B.GMAC_DEALER_USER_FLG ='D'
AND EXISTS (SELECT USER_ID FROM GSS_USER_RETAILER_MAP C
WHERE C.RETAILER_ID = 'RMAKHOSO' AND C.USER_ID = B.USER_ID AND C.BELONGS_TO = 'Y')
START WITH A.PARENT_USER_ID IN ( SELECT USER_ID FROM PARENT_QUERY )
CONNECT BY PRIOR A.USER_ID = A.PARENT_USER_ID
)
START WITH PARENT_USER_ID IS NULL
CONNECT BY PRIOR USER_ID = PARENT_USER_ID



However, on modifying the query a little bit by taking the SELECT which encloses the WITH AS Clause and putting it internally, the query runs fine. The modified query is also pasted below:

WITH PARENT_QUERY AS(
SELECT a.USER_ID, a.USER_ID AS PATH, B.USER_NM, 1 AS MAX_LEVEL,NULL PARENT_USER_ID FROM GSS_USER_RETAILER_MAP A, GSS_USER B
WHERE A.USER_ID = B.USER_ID AND A.RETAILER_ID= 'RMAKHOSO' AND B.GMAC_DEALER_USER_FLG = 'D' AND B.STATUS='ACTIVE'
AND EXISTS (SELECT C.PARENT_USER_ID FROM GSS_USER_HIERARCHY C WHERE C.PARENT_USER_ID=B.USER_ID
START WITH C.PARENT_USER_ID = 'MULTIDEALER1' CONNECT BY PRIOR C.USER_ID = C.PARENT_USER_ID)
AND NOT EXISTS (SELECT D.USER_ID FROM GSS_USER_HIERARCHY D WHERE D.USER_ID=B.USER_ID START WITH PARENT_USER_ID = 'MULTIDEALER1' CONNECT BY PRIOR USER_ID = PARENT_USER_ID)
UNION
SELECT E.USER_ID , E.USER_ID AS PATH, E.USER_NM, 1 AS MAX_LEVEL,NULL PARENT_USER_ID
FROM GSS_USER E , GSS_USER_RETAILER_MAP F WHERE E.USER_ID=F.USER_ID
AND F.RETAILER_ID= 'RMAKHOSO' AND E.GMAC_DEALER_USER_FLG = 'D' AND E.STATUS='ACTIVE' AND E.USER_ID= 'MULTIDEALER1'
)
SELECT PATH AS USER_ID, USER_NM, LEVEL
FROM (
SELECT USER_ID, PATH, USER_NM, MAX_LEVEL, PARENT_USER_ID FROM PARENT_QUERY
UNION
SELECT A.USER_ID, LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(A.USER_ID,'/') AS PATH, B.USER_NM, LEVEL+1 AS MAX_LEVEL, A.PARENT_USER_ID
FROM GSS_USER B, GSS_USER_HIERARCHY A WHERE B.USER_ID=A.USER_ID AND UPPER(B.STATUS) = 'ACTIVE' AND B.GMAC_DEALER_USER_FLG ='D'
AND EXISTS (SELECT USER_ID FROM GSS_USER_RETAILER_MAP C
WHERE C.RETAILER_ID = 'RMAKHOSO' AND C.USER_ID = B.USER_ID AND C.BELONGS_TO = 'Y')
START WITH A.PARENT_USER_ID IN ( SELECT USER_ID FROM PARENT_QUERY )
CONNECT BY PRIOR A.USER_ID = A.PARENT_USER_ID
)
START WITH PARENT_USER_ID IS NULL
CONNECT BY PRIOR USER_ID = PARENT_USER_ID


Please let me know if you have encountered this error before and what is the resolution for this.

Regards
Keshav
Re: ora-00600 during select query execution... [message #316322 is a reply to message #316320] Thu, 24 April 2008 11:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
SQL> !oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number


For this error you should contact oracle support.

This is just for your information. What you are facing is already an reported bug in oracle and presumably you are in Oracle 9i. And how did I find this information, just google it. It's that simple. But to get further information you should loging to metalink and you can do it only when you have proper authentication.
Quote:
2796802 OERI[QKAJOI:1] from SQL using WITH clause with query rewrite

Regards

Raj

[Updated on: Thu, 24 April 2008 11:16]

Report message to a moderator

Re: ora-00600 during select query execution... [message #316538 is a reply to message #316322] Fri, 25 April 2008 10:41 Go to previous messageGo to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Thanks a lot Rajaram. But am wondering as to why i am getting this error when the Metalink says that this bug is fixed in version 9.2.0.4 while i am using 9.2.0.8.
Re: ora-00600 during select query execution... [message #316541 is a reply to message #316538] Fri, 25 April 2008 10:46 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this is a bug it may or may not be fixed by the fix.
If it is not fixed then you have to open a SR.
Why is an irrelevant question, nobody can answer it but Oracle with all the trace and dump you can provide them.

Regards
Michel
Previous Topic: Can DBA log in to any schema ?
Next Topic: How to campare string in PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 23:11:07 CST 2016

Total time taken to generate the page: 0.25511 seconds