cool 9i feature - order SIBLINGS by ...

Thomas Kyte

unread,

Jul 10, 2001, 10:59:12 AM7/10/01

to

Thought I might post some of the 9i features you probably won't read about in
the press releases (as I find them)....

Order siblings by for CONNECT by statements -- you can order a connect by and
preserve the hirearchy....

sc...@TKYTE901.US.ORACLE.COM> select lpad('*', level, '*' ) || ename ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order SIBLINGS by ename
6 /

ENAME
------------------------------
*KING
**BLAKE
***ALLEN
***JAMES
***MARTIN
***TURNER
***WARD
**CLARK
***MILLER
**JONES
***FORD
****SMITH
***SCOTT
****ADAMS

14 rows selected.

sc...@TKYTE901.US.ORACLE.COM> select lpad('*', level, '*' ) || ename ename
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order SIBLINGS by ename DESC
6 /

ENAME
------------------------------
*KING
**JONES
***SCOTT
****ADAMS
***FORD
****SMITH
**CLARK
***MILLER
**BLAKE
***WARD
***TURNER
***MARTIN
***JAMES
***ALLEN

14 rows selected.

--
Thomas Kyte (tk...@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

Thomas Kyte

unread,

Jul 10, 2001, 11:14:39 AM7/10/01

to

In article <9if58...@drn.newsguy.com>, Thomas says...
>

sc...@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

Jonathan Lewis

unread,

Jul 10, 2001, 1:36:29 PM7/10/01

to

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 <9if64...@drn.newsguy.com>...

Jonathan Lewis

unread,

Jul 10, 2001, 2:23:13 PM7/10/01

to

I should have started that last post with the
comment:

Thanks very much for the information,
please keep them coming.

One of my gripes about Oracle is that many of
the most useful changes are little things that
are never glamorous enough to make it to the
press releases or 'new features' summaries.

--
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.

Jonathan Lewis wrote in message
<994786401.8669.0...@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
>
>

Mark Townsend

unread,

Jul 10, 2001, 11:42:58 PM7/10/01

to

in article 994789199.7832.0...@news.demon.co.uk, Jonathan
Lewis at jona...@jlcomp.demon.co.uk wrote on 7/10/01 11:23 AM:

> One of my gripes about Oracle is that many of
> the most useful changes are little things that
> are never glamorous enough to make it to the
> press releases or 'new features' summaries.

Strangely enough - mine too - and unfortunately it's my job to know what is
or isn't in a release :-) This one got by me. Damn developers.

You may also want to check OTN's Oracle9i Database Daily Feature - see
http://otn.oracle.com/products/oracle9i/daily/Jul10.html and the archive at
http://otn.oracle.com/products/oracle9i/daily/content.html - there are 474
new 'features' in Oracle9i, so the series should be winding up about the
time Oracle10i is ready :-)

Ian Ledzion

unread,

Jul 11, 2001, 3:13:16 AM7/11/01

to

Thanks a lot, Thomas, this one means I can drop some code from the app which
did just that, so one less headache.

Don't stop!

"Thomas Kyte" <tk...@us.oracle.com> wrote in message
news:9if58...@drn.newsguy.com...

Jim Kennedy

unread,

Jul 11, 2001, 9:21:10 PM7/11/01

to

Yeah. But don't stop the features just because of that! Keep up the great
job people. BTW, I loved your book, Jonathan. Tom, I'm only on page 200 of
your book; but I love it so far.
Jim
"Mark Townsend" <markbt...@home.com> wrote in message
news:B7711953.8C99%markbt...@home.com...