title: How to retrieve PL/SQL record type in SQL?
body: <p>A shared package exists which defines a student record type and a function which returns a student:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE shared.student_utils IS
    --aggregates related data from several tables
    TYPE student_rec IS RECORD (
      id       student.id%TYPE,
      username student.username%TYPE,
      name     student.name%TYPE,
      phone    phone.phone%TYPE
      /*etc.*/
    );
  
    FUNCTION get_student(student_id IN student.id%TYPE) RETURN student_rec;
  END;
  </code></pre>
  
  <p>Now, I'm writing a package that provides a API for an Apex application to consume. In particular, I need to provide the student record and other relevant data in a format that can be selected via SQL (and displayed in a report page in Apex.)</p>
  
  <p>So far I've been trying to find the most direct way to select the data in SQL. Obviously, a record type cannot be used in SQL, so my quick-and-dirty idea was to define a table type in my package spec and a <code>PIPELINED</code> function in my package spec/body:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE my_schema.api IS
    TYPE student_tab IS TABLE OF shared.student_utils.student_rec;
  
    FUNCTION get_all_student_data(student_id IN student.id%TYPE) RETURN student_tab PIPELINED;
  END;
  /
  CREATE OR REPLACE PACKAGE BODY my_schema.api IS
    FUNCTION get_all_student_data(student_id IN student.id%TYPE) RETURN student_tab PIPELINED IS
    BEGIN
      PIPE ROW(shared.student_utils.get_student(student_id));
    END;
  END;
  </code></pre>
  
  <p>...which lets me select it like so:</p>
  
  <pre><code>SELECT * FROM TABLE(my_schema.api.get_all_student_data(1234));
  </code></pre>
  
  <p>This works, but building a pipelined table just for one row is overkill, and Oracle's explain plan seems to agree.</p>
  
  <p>Supposedly in Oracle 12c, there should be more options available:</p>
  
  <blockquote>
    <p><a href="https://docs.oracle.com/database/121/LNPLS/release_changes.htm#GUID-57E439FB-B196-46CB-857C-0ADAB32D9EA0" rel="nofollow noreferrer">More PL/SQL-Only Data Types Can Cross PL/SQL-to-SQL Interface</a></p>
  </blockquote>
  
  <p>...but I can't seem to figure it out in my scenario. Changing the function to:</p>
  
  <pre><code>FUNCTION get_all_student_data RETURN student_tab IS
    r_student_tab student_tab;
  BEGIN
    r_student_tab(1) := shared.student_utils.get_student(student_id);
  
    RETURN r_student_tab;
  END;
  </code></pre>
  
  <p>...will compile, but I cannot <code>SELECT</code> from it as before.</p>
  
  <p>OK, enough rambling, here's my actual question - what is the most direct method to call a PL/SQL function that returns a record type and select/manipulate the result in SQL?</p>