title: recursive oracle sql to identify a value
body: <p>I have a table TABA(PK:NAME) that stores NAME,NAME_TYPE,SOURCE this way:</p>
  
  <pre><code>NAME  NAME_TYPE SOURCE
  ----  --------- ------
  Name1 Category  S1
  Name2 Category  S2
  Name3 Datamart  
  Name4 Category  S1
  Name5 Datamart
  Name6 Datamart
  Name7 Category  S3
  </code></pre>
  
  <p>Note above: Only if the NAME_TYPE = Category, then the source is present in the data.</p>
  
  <p>I have another table TABA_PARENT that stores NAME,PARENT_NAME with the relationship between the NAME columns based on the name_type this way.
  Datamart to Category relationship is one-to-many.</p>
  
  <pre><code>  NAME   PARENT_NAME
      -----  -----------
      Name3  Name1
      Name3  Name2
      Name3  Name4
      Name3  Name5
      Name5  Name1
      Name5  Name6
      Name6  Name7
  </code></pre>
  
  <p>My requirement is to get the SOURCE for TABA where NAME_TYPE = Datamart(currently, it is absent in TABA)</p>
  
  <p>Expected Output:</p>
  
  <pre><code>SOURCE column for Name3
  -----------------------
  
  S1,S2,S3
  </code></pre>
  
  <p>The trick is to recursively deduce the SOURCE for the Name3 until it maps to a Category in TABA_PARENT.</p>
  
  <p>In the above example:</p>
  
  <p>Name3 is mapped to PARENT_NAME Name1,Name2,Name4,Name5.
  3 of these(name1,Name2,Name4) are of name_type = Category and hence the distinct source is available in TABA -- S1,S2
  The fourth PARENT_NAME Name5 is a name_type Datamart(source info not available) and needs to be further expanded until it reaches name_type = Category.</p>
  
  <p>We have the information that Name5 is mapped to PARENT_NAME Name1,Name6.
  Name1 is a Category and hence source can be deduced.
  Name6 is again a Datamart.</p>
  
  <p>But, Name6 ultimately is mapped to Name7 which is a Category, hence source is available -- S3</p>
  
  <p>As shown above, all the mappings have to be recursively resolved until they reach name_type Category to identify the distinct sources.</p>
  
  <pre><code>Expected Result: S1,S2,S3 
  </code></pre>
  
  <p>I am trying if this can be done using listagg or something similar(small pl/sql code would also be ok, but prefer a sinlge select if possible)
  I am having difficulty to recursively do this.
  Any help would be much appreciated.</p>