Re: Innocent SQL causes an imminent process death..

"Unemployed in the Valley" <rembus_at_mail.ru> wrote in message news:5ec5bf40.0210031003.2f3fd6a6_at_posting.google.com...
> I wonder if anyone ever encountered something like this:
>
> Environment - Oracle 8.1.6 on Solaris 2.8
> Table "relations" defines the node hierarchy.
>
> SQL> connect username/password_at_server
> Connected.
> SQL> desc relations;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> REC_ID NOT NULL NUMBER
> PARENT_NODE_ID NOT NULL NUMBER
> CHILD_NODE_ID NOT NULL NUMBER
> CREATION_DATE NOT NULL DATE
>
> SQL> select count(*)
> 2 from relations
> 3 start with parent_node_id=3
> 4 connect by prior child_node_id=parent_node_id
> 5 and prior child_node_id in (select 3 from dual)
> 6 ;
> select count(*)
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel

I get the same results as Jonathan with 9.2. If you rewrite as

select count(*)

     from relations
   start with parent_node_id=3
   connect by prior child_node_id=parent_node_id    and prior child_node_id in (3)

or

select count(*)

     from relations
   start with parent_node_id=3
   connect by prior child_node_id=parent_node_id    and prior child_node_id =3

You should get the correct results for this somewhat strange statement.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************