Folder Name   FolderID   ParentID
---------------------------------
Folder1          1        Root
Folder2          2           1  
Folder3          3           2
Folder4          4           1
Folder5          5           4
Folder6          6           1

Each folder gets a unique id(FolderID), and an ID that links it to a previous (or root) folder(ParentID).

The above table can be read as:  Folder1/Folder2/Folder3 
Folder1/Folder4/Folder5 
Folder1/Folder6

sql>select * from t;
 
       ID    PARENT NAME
--------- --------- ----------
        1         0 Folder 1
        2         1 Folder2
        3         2 Folder 3
        4         1 Folder 4
        5         4 Folder 5
        6         1 Folder 6
 
6 rows selected.
 
sql>select sys_connect_by_path(name, '/')
  2    from t
  3   where parent = 0 
  4   start with id = 5
  5   connect by prior parent = id;
 
SYS_CONNECT_BY_PATH(NAME,'/')
------------------------------------------------------------------------------
/Folder 5/Folder 4/Folder 1
 
1 row selected.

sql>create or replace function showpath(p_id in t.id%type)
  2    return varchar2
  3  is
  4    v_path  varchar2(256);
  5  begin
  6    for r in (select name
  7                from t
  8               start with id = p_id
  9               connect by prior parent = id
 10               order by level desc) loop
 11                
 12      v_path := v_path || r.name || '/';            
 13    end loop;
 14    
 15    return (rtrim(v_path, '/'));
 16  end;
 17  /
 
Function created.
 
sql>select id, parent, showpath(id) path from t;
 
       ID    PARENT PATH
--------- --------- ------------------------------
        1         0 Folder 1
        2         1 Folder 1/Folder2
        3         2 Folder 1/Folder2/Folder 3
        4         1 Folder 1/Folder 4
        5         4 Folder 1/Folder 4/Folder 5
        6         1 Folder 1/Folder 6
 
6 rows selected.
 
sql>var x varchar2(30)
sql>exec :x := showpath(5)
 
PL/SQL procedure successfully completed.
 
sql>print x
 
X
--------------------------------
Folder 1/Folder 4/Folder 5