Re: Innocent SQL causes an imminent process death..

Your SQL isn't that innocent.
Your table definition could be wrong too. Depends on wether these id's are ForeignKey's to another table or parentid is a FK to the relations table. If both id's are FK's then i can understand why they are both mandatory. If parentid is a FK to the relations table then parent id should be optional.

There must be a root in your tree, a record where fatherid is empty, the starting point of the HIERARCHICAL tree. If you are using the connect by - clause, then be sure that the data is structured hierarchically.

Suppose this is a subset of the table relations childid - parentid
1

2 - 1
3 - 1
4 - 3
5 - 3

This is how the query should be

select ....
from relations
start with childid = 3
connect by prior childid = parentid

This query will retrieve child 4 and child 5

"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 tried it on 3 different servers with the same results from Windows &
> Solaris clients.
Received on Thu Oct 03 2002 - 14:08:58 CDT