Home » SQL & PL/SQL » SQL & PL/SQL » problem with multiple "not in" conditions in query (Oracle 10g)
problem with multiple "not in" conditions in query [message #430720] Thu, 12 November 2009 00:45 Go to next message
nazeer4u
Messages: 1
Registered: November 2009
Junior Member
I am getting the following error when trying to execute a query with multiple 'not in' conditions. There are about 900 values in each condition (Total 3 conditions). I am quite sure that there is not problem with syntax as it is working when few of the values in the 'not in' condition are removed.

ORA-00600: internal error code, arguments: KGHAL02,0x0],[,],[,],[,[]

The query goes like this.

Select specimen_id, specimen_name from specimen,entity_type where specimen.entity_type_code = entity_type.entity_type_code where entity_type.entity_type = 'Sample' and specimen.specimen_id
not in ('A','B'...............) and specimen.specimen_id not in ('X','Y',............) and specimen.specimen_id not in ('AA','BB');

There are multiple not in conditions on same column as oracle does not support more than 1000 values in 'in' or 'not in' condition
Re: problem with multiple "not in" conditions in query [message #430725 is a reply to message #430720] Thu, 12 November 2009 01:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
This may help you.
ORA-00600

regards,
Delna
Re: problem with multiple "not in" conditions in query [message #430743 is a reply to message #430720] Thu, 12 November 2009 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ORA-600 => Contact Oracle Support

It's an internal error, meaning that something has gone wrong.

There's an Ora-600 lookup tool aavailable in Metalink that provides details about the more common Ora-600 errors and ways of fixing them.
Re: problem with multiple "not in" conditions in query [message #430844 is a reply to message #430720] Thu, 12 November 2009 08:39 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
nazeer4u wrote on Thu, 12 November 2009 00:45

There are multiple not in conditions on same column as oracle does not support more than 1000 values in 'in' or 'not in' condition


Why not you store those values in a table and then use a select?
Previous Topic: Regarding High Water Mark and Index
Next Topic: Incremental counter required
Goto Forum:
  


Current Time: Thu Dec 08 06:25:04 CST 2016

Total time taken to generate the page: 0.18331 seconds