Re: Urgent..need help for pl/sql procedure

You should look at the documentation on the "connect by" clause. You would probably only one select statement then to return the rows to you in hierarchical order.

gcj_at_hotmail.com wrote:

> trying to produce an indented list output based on following criteria:
> 1) I have a master table that stores document_numbers and a parent/child
> table storing the relationship.
> 2) Based on a passed document_number, I need to get all child docs for the
> param doc_num and explode 15 levels and report the results in an indented
> format like:
>
> lvl doc_no
> 1 passed doc_no
> 2 direct child doc
> 3 grand child
> 3 grand child
> 4 child
> 2 direct child
>
> I wrote something like this:
>
> PROCEDURE load_200(pDocNum VARCHAR2)
> IS
> vParentNum VARCHAR(25);
> vLv_l NUMBER(2);
>
> CURSOR c_Level2
> IS
> SELECT cp_child_num
> FROM cpfile
> WHERE cp_parent_num = vParentNum;
>
> vLvl_2DocNum c_Level2%ROWTYPE;
>
> CURSOR c_Level3
> IS
> SELECT cp_child_num
> FROM cpfile
> WHERE cp_parent_num = vParentNum;
>
> vLvl_3DocNum c_Level3%ROWTYPE;
> ...........15 of these declarations
> Begin
> --Level 1
> vParentNum := pDocNum;
> vLvl := 1;
>
> INSERT INTO r200_temp(docnum,lvl) VALUES (vParentNum, vLvl);
>
> -- Level 2
> OPEN c_Level2;
> LOOP
> FETCH c_Level2 INTO vLvl2DocNum;
> IF
> c_Level2%NOTFOUND
> THEN
> vParentNum := pDocNum;
> CLOSE c_Level2;
> EXIT;
> ELSE
> vParentNum := vLvl2DocNum.cp_child_num;
> vLvl := 2;
> INSERT INTO r200_temp(docnum,lvl) VALUES (' '||vParentNum,
> vLvl);
> COMMIT;
> .......and so forth, problem is my output starts with lvl 2
> any suggestions??
>
> jacko4418
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Jan 22 1999 - 22:25:49 CET