Re: cool 9i feature - order SIBLINGS by ... and sys_connect_by

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 10 Jul 2001 18:36:29 +0100
Message-ID: <994786401.8669.0.nnrp-07.9e984b29@news.demon.co.uk>

Of course, one shouldn't really mention
undocumented features, but the

sys_connect_by_path( ename, '/' )
syntax is also valid in 8.1.7, provided
you have set

_new_connect_by_enabled = true.

(Now I just have to figure out the
other 23 SYS_xxxx functions :)

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Thomas Kyte wrote in message <9if64v0brg_at_drn.newsguy.com>...


>In article <9if58008jt_at_drn.newsguy.com>, Thomas says...

>>

>

>

>scott_at_TKYTE901.US.ORACLE.COM> select lpad('*', level, '*' ) || ename ename,

>  2         sys_connect_by_path( ename, '/' ) path

>  3  from emp

>  4  start with mgr is null

>  5  connect by prior empno = mgr

>  6  order SIBLINGS by ename DESC

>  7  /

>

>ENAME                          PATH

>------------------------------ ----------------------------------------

>*KING                          /KING

>**JONES                        /KING/JONES

>***SCOTT                       /KING/JONES/SCOTT

>****ADAMS                      /KING/JONES/SCOTT/ADAMS

>***FORD                        /KING/JONES/FORD

>****SMITH                      /KING/JONES/FORD/SMITH

>**CLARK                        /KING/CLARK

>***MILLER                      /KING/CLARK/MILLER

>**BLAKE                        /KING/BLAKE

>***WARD                        /KING/BLAKE/WARD

>***TURNER                      /KING/BLAKE/TURNER

>***MARTIN                      /KING/BLAKE/MARTIN

>***JAMES                       /KING/BLAKE/JAMES

>***ALLEN                       /KING/BLAKE/ALLEN

>

>14 rows selected.

>

>--

>Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/

>Expert one on one Oracle, programming techniques and solutions for Oracle.

>http://www.amazon.com/exec/obidos/ASIN/1861004826/

>Opinions are mine and do not necessarily reflect those of Oracle Corp

>

Received on Tue Jul 10 2001 - 12:36:29 CDT

Re: cool 9i feature - order SIBLINGS by ... and sys_connect_by_path