Create or Replace Package emp20 is
   
TYPE t_emptyptab is table of employees%ROWTYPE;

Type ref_cur is ref cursor;
    return employees%ROWTYPE;

Function getEmpNTab2 (pValue in Varchar2)            
    Return t_emptyptab PIPELINED;

End emp20;
/

Create or Replace Package Body emp20 is

Function getEmpNTab2 (pValue in Varchar2)           
    Return t_emptyptab PIPELINED 
    is
    
    vSQL    Varchar2(2000);
    vTab    t_emptyptab;
Begin
    if pValue = 'alt' then
        vSQL    := 'SELECT e.empno employee_id, Null first_name, e.ename last_name '
                   ||      ', Null email, Null phone_number, e.hiredate hire_date '
                   ||      ', e.job job_id, e.sal salary '
                   ||      ', ROUND(e.comm/e.sal,2),99/100) commission_pct '
                   ||      ', e.mgr manager_id, e.deptno department_id '
                   ||      ', FROM emp e ';
    elsif pValue = 'neu' then
        vSQL    := 'SELECT e.* FROM employees e' ;
    end if;
    
    EXECUTE IMMEDIATE vSQL BULK COLLECT into vTab;
    FOR i in vTab.first..vTab.last loop
            pipe row (vTab(i));
        end loop;
        return;
end getEmpNTab2;
  
end emp20;
/

Declare 
    vTab emp20.t_emptyptab  := emp20.t_emptyptab();
    idx integer;
    
Begin
    vTab    := emp20.getEmpNTab2('alt');
    
    idx := vTab.first;
    
    loop
        exit when idx = vTab.last;
            dbms_output.put_line(vTab(idx).employee_id || ' ' || vTab(idx).last_name||' '|| vTab(idx).first_name);
       idx  := vTab.next(idx);
    end loop;
end;

/

SQL> Create or Replace Package emp20 is
  2
  3  TYPE t_emptyptab is table of employees%ROWTYPE;
  4
  5  Type ref_cur is ref cursor;
  6      return employees%ROWTYPE;
  7
  8  Function getEmpNTab2 (pValue in Varchar2)
  9      Return t_emptyptab PIPELINED;
 10
 11  End emp20;
 12  /

Package created.

SQL> Create or Replace Package Body emp20 is
  2
  3  Function getEmpNTab2 (pValue in Varchar2)
  4      Return t_emptyptab PIPELINED
  5      is
  6
  7      vSQL    Varchar2(2000);
  8      vTab    t_emptyptab;
  9  Begin
 10      if pValue = 'alt' then
 11          vSQL    := 'SELECT e.empno employee_id, Null first_name, e.ename last_name '
 12                     ||      ', Null email, Null phone_number, e.hiredate hire_date '
 13                     ||      ', e.job job_id, e.sal salary '
 14                     ||      ', ROUND(e.comm/e.sal,2),99/100) commission_pct '
 15                     ||      ', e.mgr manager_id, e.deptno department_id '
 16                     ||      ', FROM emp e ';
 17      elsif pValue = 'neu' then
 18          vSQL    := 'SELECT e.* FROM employees e' ;
 19      end if;
 20
 21      EXECUTE IMMEDIATE vSQL BULK COLLECT into vTab;
 22      FOR i in vTab.first..vTab.last loop
 23              pipe row (vTab(i));
 24          end loop;
 25          return;
 26  end getEmpNTab2;
 27
 28  end emp20;
 29  /

Package body created.

SQL> Declare
  2      vTab emp20.t_emptyptab  := emp20.t_emptyptab();
  3      idx integer;
  4
  5  Begin
  6      vTab    := emp20.getEmpNTab2('alt');
  7
  8      idx := vTab.first;
  9
 10      loop
 11          exit when idx = vTab.last;
 12              dbms_output.put_line(vTab(idx).employee_id || ' ' || vTab(idx).last_name||' '|| vTab(idx).first_name);
 13         idx  := vTab.next(idx);
 14      end loop;
 15  end;
 16
 17  /
    idx := vTab.first;
         *
ERROR at line 8:
ORA-06550: line 8, column 10:
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

SQL> Create or Replace Package emp20 is
  2
  3  TYPE t_emptyptab is table of employees%ROWTYPE;
  4
  5  Type ref_cur is ref cursor;
  6      return employees%ROWTYPE;
  7
  8  Function getEmpNTab2 (pValue in Varchar2)
  9      Return t_emptyptab ;
 10
 11  End emp20;
 12  /

Package created.

SQL> sho err
No errors.
SQL> Create or Replace Package Body emp20 is
  2
  3  Function getEmpNTab2 (pValue in Varchar2)
  4      Return t_emptyptab
  5  is
  6      vSQL    Varchar2(2000);
  7      vTab    t_emptyptab;
  8  Begin
  9      if pValue = 'alt' then
 10          vSQL    := 'SELECT e.empno employee_id, Null first_name, e.ename last_name '
 11                     ||      ', Null email, Null phone_number, e.hiredate hire_date '
 12                     ||      ', e.job job_id, e.sal salary '
 13                     ||      ', ROUND(e.comm/e.sal,2),99/100) commission_pct '
 14                     ||      ', e.mgr manager_id, e.deptno department_id '
 15                     ||      ', FROM emp e ';
 16      elsif pValue = 'neu' then
 17          vSQL    := 'SELECT e.* FROM employees e' ;
 18      end if;
 19
 20      EXECUTE IMMEDIATE vSQL BULK COLLECT into vTab;
 21      return vTab;
 22  end getEmpNTab2 ;
 23
 24  end emp20;
 25  /

Package body created.

SQL> sho err
No errors.
SQL> Declare
  2      vTab emp20.t_emptyptab  := emp20.t_emptyptab();
  3      idx integer;
  4
  5  Begin
  6      vTab    := emp20.getEmpNTab2('alt');
  7
  8      idx := vTab.first;
  9
 10      loop
 11          exit when idx = vTab.last;
 12              dbms_output.put_line(vTab(idx).employee_id || ' ' || vTab(idx).last_name||' '|| vTab(idx).first_name);
 13         idx  := vTab.next(idx);
 14      end loop;
 15  end;
 16  /
Declare
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "MICHEL.EMP20", line 20
ORA-06512: at line 6

Declare 
    vTab    emp20.t_emptyptab;
Begin
    vTab    := emp20.getEmpNTab2('alt');
    For idx IN 1 .. vTab.COUNT
    loop
       dbms_output.put_line(vTab(idx).employee_id || ' ' || vTab(idx).last_name||' '|| vTab(idx).salary);
    end loop;
end;

ORA-06512: at "HR.EMP20", line 21

Create or Replace Package test2 is
   
TYPE t_emptyptab is table of employees%ROWTYPE;

Type ref_cur is ref cursor;
    return employees%ROWTYPE;

Function getEmpNTab2 (pValue in Varchar2)             
    Return t_emptyptab;

End test2;
/

Create or Replace Package Body test2 is

Function getEmpNTab2 (pValue in Varchar2)             
    Return t_emptyptab
    is
    
    vSQL    Varchar2(2000);
    vTab    t_emptyptab;
Begin
    if pValue = 'oldtab' then
        vSQL    := 'SELECT e.empno employee_id, Null first_name, e.ename last_name '
                   ||      ', Null email, Null phone_number, e.hiredate hire_date '
                   ||      ', e.job job_id, e.sal salary '
                   ||      ', LEAST(ROUND(e.comm/e.sal,2),99/100) commission_pct '
                   ||      ', e.mgr manager_id, e.deptno department_id '
                   ||      ' FROM emp e ';
    elsif pValue = 'newtab' then
        vSQL    := 'SELECT e.* FROM employees e' ;
    end if;
    
    EXECUTE IMMEDIATE vSQL BULK COLLECT into vTab;
    --FOR i in vTab.first..vTab.last loop
            --pipe row (vTab(i));
        --end loop;
    Return vTab;
end getEmpNTab2;
  
end test2;
/


Declare 
    vTab test2.t_emptyptab  := test2.t_emptyptab();
    idx integer;
    
Begin
    vTab    := test2.getEmpNTab2('oldtab');
    idx := vTab.first;
    
    loop
        exit when idx = vTab.last;
            dbms_output.put_line(vTab(idx).employee_id || '  ' || vTab(idx).last_name||'  '|| vTab(idx).salary);
       idx  := vTab.next(idx);
    end loop;
end;

/

SQL> Create or Replace Package test2 is
  2    procedure getEmpNTab2 (pValue in Varchar2, pCurs in out sys_refcursor);
  3  End test2;
  4  /

Package created.

SQL> sho err
No errors.
SQL> Create or Replace Package Body test2 is
  2    procedure getEmpNTab2 (pValue in Varchar2, pCurs in out sys_refcursor)
  3    is
  4    Begin
  5      if pValue = 'oldtab' then
  6        open pCurs for
  7          'SELECT e.empno employee_id, Null first_name, e.ename last_name '
  8          ||        ', Null email, Null phone_number, e.hiredate hire_date '
  9          ||      ', e.job job_id, e.sal salary '
 10          ||      ', LEAST(ROUND(e.comm/e.sal,2),99/100) commission_pct '
 11          ||      ', e.mgr manager_id, e.deptno department_id '
 12          ||      ' FROM emp e ';
 13      elsif pValue = 'newtab' then
 14        open pCurs for 'SELECT e.* FROM employees e' ;
 15      end if;
 16    end getEmpNTab2;
 17  end test2;
 18  /

Package body created.

SQL> sho err
No errors.
SQL> Declare
  2    vTab sys_refcursor;
  3    rec  employees%ROWTYPE;
  4  begin
  5    test2.getEmpNTab2('oldtab',vTab);
  6    dbms_output.put_line ('Old table:');
  7    loop
  8      fetch Vtab into rec;
  9      exit when vTab%notfound;
 10      dbms_output.put_line('  '||rec.employee_id||'  '||rec.last_name||'  '||rec.salary);
 11    end loop;
 12    close vTab;
 13    dbms_output.put_line ('New table:');
 14    test2.getEmpNTab2('newtab',vTab);
 15    loop
 16      fetch Vtab into rec;
 17      exit when vTab%notfound;
 18      dbms_output.put_line('  '||rec.employee_id||'  '||rec.last_name||'  '||rec.salary);
 19    end loop;
 20    close vTab;
 21  end;
 22  /
Old table:
  7369  SMITH  800
  7499  ALLEN  1600
  7521  WARD  1250
  7566  JONES  2975
  7654  MARTIN  1250
  7698  BLAKE  2850
  7782  CLARK  2450
  7788  SCOTT  3000
  7839  KING  5000
  7844  TURNER  1500
  7876  ADAMS  1100
  7900  JAMES  950
  7902  FORD  3000
  7934  MILLER  1300
New table:
  100  King  24000
  101  Kochhar  17000
  102  De Haan  17000
  103  Hunold  9000
  104  Ernst  6000
  105  Austin  4800
  106  Pataballa  4800
  107  Lorentz  4200
...
  204  Baer  10000
  205  Higgins  12008
  206  Gietz  8300

PL/SQL procedure successfully completed.