Copy Tree Structure

From: Charanjiv <charanjiv.kalha_at_india.techspan.com>
Date: 3 May 2002 00:49:16 -0700
Message-ID: <1c0bb9d5.0205022349.5ba68370@posting.google.com>

Hi
I have a table with a typical tree structure - columns

Folder Id , Parent Folder Id

What i want is to copy a folder (with all its subfolders) to any part of the tree. Presently we are using a Procedure in which we use a TABLE OF NUMBER type to store the old and new folder ids and search thru that to change the parent folder ids for all nodes. Is that a better way?

One more thing - We are assuming that the the rows will always be ordered parent first, then child. That is the first record will always will be the record we state in the START WITH clause. Is that ok?

Regards,
Charanjiv

CREATE OR REPLACE PROCEDURE STT.COPY_FOLDERS (

	p_folder_id IN folder.folder_id%TYPE,
	p_target_parent_folder_id IN folder.PARENT_FOLDER_ID%TYPE) 
	AS
	v_folder_id FOLDER.FOLDER_ID%TYPE;
	v_target_parent_folder_id folder.PARENT_FOLDER_ID%TYPE;
	v_parent_folder_id FOLDER.PARENT_FOLDER_ID%TYPE;



	-- list of folders with its subfolders

	CURSOR get_subfolders( p_folder_id IN folder.folder_id%TYPE ) IS
		SELECT *
		FROM folder
		START WITH folder_id = p_folder_id
		CONNECT BY PRIOR folder_id=parent_folder_id;



	-- a type and a variable to hold the contents of the

	-- old folder id and generated folder id

	TYPE folder_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
	v_folder_id_map folder_id_type;

BEGIN
 v_target_parent_folder_id := p_target_parent_folder_id;    FOR x IN get_subfolders( p_folder_id )     LOOP

     SELECT FOLDER_ID_SEQ.NEXTVAL 
       INTO v_folder_id 
       FROM DUAL;

     v_folder_id_map(x.folder_id):= v_folder_id;

       IF v_folder_id_map.EXISTS(x.parent_folder_id) THEN
          v_parent_folder_id := v_folder_id_map(x.parent_folder_id);
      ELSE
        v_parent_folder_id := v_target_parent_folder_id;
      END IF;

         INSERT INTO FOLDER 
            ( FOLDER_ID,
	      PARENT_FOLDER_ID,
    	     )	
         VALUES (
		v_folder_id,
		v_parent_folder_id
                );

    END LOOP;
END copy_folders; Received on Fri May 03 2002 - 02:49:16 CDT