Oracle 8i, Building Hierarchical Datasets, XML

[manirana007]

Hello All,

I have heard that you can build hierarchical datasets in Oracle 9i, but was wondering if this functionality is available in Oracle 8i. I found one article saying that Oracle was planning to implement it in Oracle 8i. Are there any good resources for building hierarchical datasets, with sample SQL. I believe that the result is a xml file that can be parsed for data. Any helpful tips on this topic would be appreciated. Thanks.

-manirana007

 

[SantaMufasa]

Manirana,

Oracle's ability to traverse hierarchical data structures has been available since before I can remember, via Oracle's proprietary "CONNECT BY...START WITH" syntax. The addition of XML support is more recent, and I am not aware that Oracle 8i had any XML support.

But here is proof of concept of the hierarchical portion of your question:

Section 1 -- Sample hierarchical employee data (employee records (PK: ID) contain hierarchical references (FK: MANAGER_ID) to their managers:

select id, last_name, manager_id from s_emp;

ID LAST_NAME    MANAGER_ID
-- ------------ ----------
 1 Velasquez
 2 Ngao                  1
 3 Nagayama              1
 4 Quick-To-See          1
 5 Ropeburn              1
 6 Urguhart              2
 7 Menchu                2
 8 Biri                  2
 9 Catchpole             2
10 Havel                 2
11 Magee                 3
12 Giljum                3
13 Sedeghi               3
14 Nguyen                3
15 Dumas                 3
16 Maduro                6
17 Smith                 6
18 Nozaki                7
19 Patel                 7
20 Newman                8
21 Markarian             8
22 Chang                 9
23 Patel                 9
24 Dancs                10
25 Schwartz             10

25 rows selected.

Section 2 -- Hierarchical traversal of Employee data, producing textual org chart:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Oracle Corporation makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set pagesize 0
set verify off
select lpad(' ',level * 3) || last_name x
  from s_emp
connect by prior id = manager_id
start with manager_id is null
/
set pagesize 23

   Velasquez
      Ngao
         Urguhart
            Maduro
            Smith
         Menchu
            Nozaki
            Patel
         Biri
            Newman
            Markarian
         Catchpole
            Chang
            Patel
         Havel
            Dancs
            Schwartz
      Nagayama
         Magee
         Giljum
         Sedeghi
         Nguyen
         Dumas
      Quick-To-See
      Ropeburn

25 rows selected.

Let us know if this was helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[manirana007]

Thanks SantaMufasa for your help. Your reply did shed some light on the topic. It's time for me to dig up some resources for more detail. If you happen to know any good ones, please recommend. Thanks.

manirana007

 

[carp]

M -
If you are referring to explicitly defined hierarchies, Oracle did implement this capability in version 8i. You can see the syntax in the SQL reference manual, and I believe there is a full discussion of implementing and using them in the Concepts manual.
In addition, in Tom Kyte's excellent book (expert one-on-one Oracle), Chapter 13, there is an example demonstrating the performance gains that are achievable through the proper use of hierarchies.