title: dynamic sql in a pipeline function oracle
body: <pre><code>create or replace function fn_master(col_name VARCHAR2) return master_file pipelined
  Is
         TYPE MYMASTER_FILE IS RECORD (
          id                         NUMBER(5),
          name                        VARCHAR2(101),
          school                      varchar2(2000)
         
     );
  
  
  
   
     TYPE MASTER_FILE_ARRAY IS TABLE OF MYMASTER_FILE
        INDEX BY PLS_INTEGER;
   
     l_info MASTER_FILE_ARRAY;
      
  begin 
  
  
  
  execute immediate 'select id, name, school from x_table where ' || col_name || '= ''xxxxxx'' ' 
  
     BULK COLLECT INTO l_info;
   
     FOR i IN 1 .. l_info.COUNT
     LOOP
                                PIPE row(master_file(               
                                      l_info(i).id,
                                      l_info(i).name ,
                                      l_info(i).school
                                    
                                  ));
     END LOOP;
  end fn_master;
  </code></pre>
  <p>here is function that I have created to accept a column from this table and print the rows returned. I have made it simplistic for the purpose of presenting it here on stack overflow.</p>
  <p>when i try to compile i am getting the error</p>
  <pre><code>PLS-00630: pipelined functions must have a supported collection return type
  </code></pre>
  <p>however when i added a return clause it tells me</p>
  <pre><code>PLS-00633: RETURN statement in a pipelined function cannot contain an expression
  PLS-00382: expression is of wrong type
  PLS-00630: pipelined functions must have a supported collection return type
  </code></pre>
  <p>can you all help me to solve this</p>