Urgent..need help for pl/sql procedure

From: <gcj_at_hotmail.com>
Date: Thu, 21 Jan 1999 14:58:46 GMT
Message-ID: <787fau$jvn$1_at_nnrp1.dejanews.com>

[Quoted] 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 Thu Jan 21 1999 - 15:58:46 CET