Recursion in Oracle

Question

I have the following table in oracle:

Parent(arg1, arg2) 

and I want the transitive closure of the parent relationship. That is, I want the following table

 Ancestor(arg1, arg2) 

How is this possible in Oracle?

I do the following:

 WITH Ancestor(arg1, arg2) AS ( SELECT p.arg1, p.arg2 from parent p UNION SELECT p.arg1 , a.arg2 from parent p, Ancestor a WHERE p.arg2 = a.arg1 ) SELECT DISTINCT * FROM Ancestor; 

I get an error

 *Cause: column aliasing in WITH clause is not supported yet *Action: specify aliasing in defintion subquery and retry Error at Line: 1 Column: 20 

How can I solve this problem without column aliases?

Answer 1

 WITH Ancestor(arg1, arg2) AS ( SELECT p.arg1, p.arg2 FROM parent p WHERE arg2 NOT IN ( SELECT arg1 FROM parent ) UNION ALL SELECT p.arg1, a.arg2 FROM Ancestor a JOIN parent p ON p.arg2 = a.arg1 ) SELECT * FROM Ancestor 

Oracle only supports recursive CTE with 11g Release 2.

In earlier versions, use the CONNECT BY :

 SELECT arg1, CONNECT_BY_ROOT arg2 FROM parent START WITH arg2 NOT IN ( SELECT arg1 FROM parent ) CONNECT BY arg2 = PRIOR arg1 

Answer 2

Oracle allows recursive queries. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html

Of course, they usually assume that hierarchical data is all in one table. Dividing it into separate tables complicates the work.