title: ORACLE return from function without creating a new type
body: <p>I'm wanting to create a function (oracle 11g) that will return multiple values but without having to creating a new TYPE.  The end goal is much more complicated than the example I'm providing, but I can do pretty much everything else except get the values to return.</p>
  
  <p>Here is an extremely simplified version of what I'm having trouble with.</p>
  
  <p>for example given the following table (employees):</p>
  
  <pre><code>emplid | emplname | emplchildren
  478    |SAM       |"GEORGE,RON"
  479    |JOSE      |"RICHARD,JANE,RACHEL"
  480    |PAM       |"JORDAN"
  </code></pre>
  
  <p>I would like the following statement <code>select CHILD_FN from employees</code> to return:</p>
  
  <pre><code>GEORGE
  RON
  RIRCHARD
  JANE
  RACHEL
  JORDAN
  </code></pre>
  
  <p>This is simplified to show the part I'm having trouble with
  Here is a sample code:</p>
  
  <pre><code>create or replace function CHILD_FN RETURN employee.emplname%TYPE IS
      chldnames employee.emplname%TYPE;
  
  CURSOR child_cur
  IS 
  Select emplchildren FROM employees;
  
  
    begin
  
        /*do complicated parsing to separate each delimited 
         value of child_cur and assign
         it to a new row in the names_col variable/
  
        /*how do I add values to the names_col variable?  I've tried
        'chldnames.extend',
        various types of 'bulk collect into chldnames'
        and they all give various errors*/
  
  
        return chldnames;
  
  end LOCAL_TEST_FN;
  </code></pre>
  
  <p>do I need to change my return type, or is it not declared properly?<br>
  I'm not completely against created a new type in the database, it's just we've gotten very far without ever having to create a new type, and the less complicated I can make things, the better. And if there is a way around it, I would prefer to learn that way and make the decision on which is better.</p>
  
  <p>Let me know if I need to provide more information.</p>