Re: PL/SQL procedure question

From: Jason Pepper <jpepper_at_steamer.u-net.com>
Date: Sat, 23 Jan 1999 11:34:05 +0000
Message-ID: <36A9B3AC.55A1B2D2_at_steamer.u-net.com>

You will need to create a temporary summary table that contains the primary column , the foreign key column and the data column as a minimum then use a treewalk to produce the report you want.

Like this..

SQL> create table documents (pid number, cid number, doc_name varchar2(50));

SQL> /

       PID        CID DOC_NAME
---------- ---------- --------------------------------------------------
        -1            Root element
         1         -1 Little Women
         2          1 Dr Dolittle
         3          1 20000 Leagues under the Sea
         4         -1 Ian Botham - My Autobiography
         5          4 Oracle made Easy !
         6         -1 Using Linux
         7          6 Teach yourself Java
         8          6 Oracle Performance Tuning
         9          6 Oracle DBA Survival Guide
        10          6 Oracle CDM Handbook

11 rows selected.
 
 

SQL> get 1
  1  select lpad(' ',4*(level-1))||doc_name Document
  2  from documents
  3  connect by prior pid=cid
  4* start with pid = -1

SQL> _at_1

DOCUMENT
--------------------------------------------------------------------------------
Root element
    Little Women
        Dr Dolittle
        20000 Leagues under the Sea
    Ian Botham - My Autobiography
        Oracle made Easy !
    Using Linux
        Teach yourself Java
        Oracle Performance Tuning
        Oracle DBA Survival Guide
        Oracle CDM Handbook

11 rows selected.
 
 

jacko4418_at_my-dejanews.com wrote:

I need to write a PL/SQL procedure that will produce an indented output based
on a child/parent relationship:  Two tables involved a master table that
stores documents doc_no is PK and  a child/parent table that stores the
associated docs for a given doc_no. The report needs to explode up to 15
levels. So for the doc_no passed to the procedure, I need the direct
descendants and each of its child records up to 15 levels. I want to
establish the levels and report them in an indented format Any ideas?--I set
up 15 cursors and looped through but I am not getting the results I need

gcj_at_hotmail.com

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

-- 
Regards

Jason
*-------------------------------------------------*
| Jason Pepper          jpepper_at_steamer.u-net.com |
|                                                 |
|   ... look out Bill, the Penguin is coming      |
|                                                 |
*-------------------------------------------------*

  Received on Sat Jan 23 1999 - 12:34:05 CET