Re: Top-n-analysis causing ORA-03113

In article <EMrRc.884$Ny6.1912_at_mencken.net.nih.gov>, hastenthunder wrote:
> Hi Experts,
>
> I'm getting a ORA-03113: end-of-file on communication channel exception on
> Oracle 10g/Red Hat Adv. Sever 3 when executing the following query:
>
> SELECT *
> FROM (SELECT person0_.person_id AS person_id0_,
> facility2_.fac_id AS fac_id1_,
> person0_.display_id AS display_id0_,
> facility2_.under_contract AS under_co3_1_
> FROM person person0_ INNER JOIN
> fac_affl affiliatio1_ ON person0_.person_id = affiliatio1_.person_id,
> facility facility2_
> WHERE affiliatio1_.fac_id = facility2_.fac_id
> ORDER BY person0_.person_id
> )
> WHERE rownum <= 10
>
> I'm really puzzled, if this is not a bug, can anyone tell me why running a
> query would cause an ORA-03113?
>
> The query seem to work OK when I take the order-by clause out.
>
> The sql looks ugly, I know, and please don't ask me why the query is written
> like this. Our developers uses hibernate to auto-generate these SQL
> statements.
>
>
> Thanks in advance for your help!

I remember that I had a similar problem once, however on 9iR2. And I am not sure anymore if it caused a ORA-01331. But anyway, I think I misspelled the name of a column (or something as weird as that) and Oracle was unable to detect that as an syntax error. I solved it when I run the inner query alone, in which case Oracle detected the badly spelled column name.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/