ORA-00600 with Oracle 10.2 / Intermedia

[Frank Schmitt]

Hi!

We're currently setting up a server for one of our customers, and our application which works flawlessly with Oracle 9.2 and 10.1 servers fails when using the Oracle fulltext index (intermedia).

Reproduce:
-- create dummy table on the database server

create table dummy_tab(
id integer,
clob1 clob,
clob2 clob
);

-- create indices on dummy_tab

create index dummy_tab_idx1 on dummy_tab(clob1)
indextype is ctxsys.context
create index dummy_tab_idx2 on dummy_tab(clob2)
indextype is ctxsys.context

-- issue query, using Direct Oracle Access

select
id
from
dummy_tab
where
(contains(clob1, :QueryTerm) > 0)
or
(contains(clob2, :QueryTerm) > 0)

This results in an ORA-00600: internal error.
The same query works fine when using other SQL query tools, so the bug seems to be DOA only.
The bug seems to ocurr only when combining two clauses with 'or'/'and' and the same bind variable - using only one clause or two clauses with different bind variables works fine.
Server: Oracle 10.2.0.1.0, Win XP SP2
Client: Delphi6, DOA 4.0.6.2, Win XP SP2

Do you have any ideas / suggestions / workarounds?

Thanks in advance & kind regards
Frank

 

[Marco Kalter]

An ORA-00600 error indicates that the Oracle Server process has crashed. Can you check if a user dump file exists on the server that corresponds to this error? I will contain some additional information about the error that may provide a clue. Feel free to send the trace file by e-mail, or post the relevant section here on the forum.

 

[Frank Schmitt]

Ok, since the trace file is about 2MB, I'm sending it to you by e-mail.

 

[Marco Kalter]

It's probably an Oracle Server bug, which you could verify by running the same query with bind variables in SQL*Plus. As a workaround you can use substitution variables instead, and modify the query slightly:

select
  id
from
  dummy_tab
where
  (contains(clob1, ':QueryTerm') > 0)
  or
  (contains(clob2, ':QueryTerm') > 0)

If the :QueryTerm variable contains any quotes, you need to replace them by 2 quotes.

 

[Frank Schmitt]

You're correct - executing the query in SQL/Plus gives the same error. I suspected DOA because the Query worked fine in Hora (our standard SQL Tool) - obviously Hora handles bind variables differently.
Using substitution variables works fine with DOA - but for the time being, we'll recommend our customers to stick to Oracle 10.1 and file a bug report with Oracle regarding Oracle 10.2

Thanks again & kind regards
Frank