title: Oracle Table Function from CTE
body: <p>I'd like to create a table function in oracle from the following query:</p>
  
  <pre><code> WITH A AS (
      SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
   )
  SELECT * FROM A;
  </code></pre>
  
  <p>'part_num' being a parameter passed into the function. I am having trouble with the syntax. This is what I have tried:</p>
  
  <pre><code>CREATE OR replace FUNCTION part_test_f(search_part IN varchar2)
  RETURN part_test_t PIPELINED 
  AS 
  
      rec PART_TEST;
  
      CURSOR cur(part_num) IS 
           WITH A AS (
              SELECT * FROM PART_TABLE WHERE PART_NO LIKE part_num
           )
          SELECT * FROM A;
  BEGIN
          FOR record IN cur(search_part) LOOP
          rec := PART_TEST(record);
          pipe row(rec);
          END LOOP;
  return;
  END;
  </code></pre>
  
  <p>I have created the types for the table and the rows. Thanks for any help.</p>
  
  <p>EDIT: I have given it another go. The declaration now looks like:</p>
  
  <pre><code>    create or replace FUNCTION part_test_f(search_part IN varchar2)
  RETURN part_test_t PIPELINED
  AS
  
      rec PART_TEST;
  
      CURSOR cur(part_num varchar2) RETURN PART_TEST IS
           WITH A AS (
              SELECT * FROM F6RD_PART WHERE PART_NO LIKE part_num
           )
          SELECT * FROM A;
  BEGIN
          FOR rec IN cur(search_part) LOOP
          pipe row(rec);
          END LOOP;
  return;
  END;
  </code></pre>
  
  <p>Now I receive an 'No more data to read from socket' error</p>