Posted By

robvl66 on 12/07/10


Tagged


Versions (?)

pipelined function consuming ref cursor


 / Published in: PL/SQL
 

  1. CREATE TABLE all_tables_plus AS SELECT * FROM all_tables WHERE 1 = 2;
  2. ALTER TABLE all_tables_plus add (prev_table_name VARCHAR2(30));
  3.  
  4.  
  5.  
  6. TYPE refcur_tables IS REF CURSOR RETURN all_tables%ROWTYPE;
  7. TYPE t_tables_plus IS TABLE OF all_tables_plus%ROWTYPE;
  8. END ref_pkg;
  9. /
  10.  
  11. CREATE OR REPLACE FUNCTION add_prev_table (cur_tables IN ref_pkg.refcur_tables)
  12. RETURN ref_pkg.t_tables_plus pipelined
  13. l_prev_table_name VARCHAR2(30) := NULL;
  14. l_tables all_tables%ROWTYPE;
  15. l_tables_plus all_tables_plus%ROWTYPE;
  16. FETCH cur_tables INTO l_tables;
  17. EXIT WHEN cur_tables%NOTFOUND;
  18. l_tables_plus.table_name := l_tables.table_name;
  19. l_tables_plus.prev_table_name := l_prev_table_name;
  20. l_prev_table_name := l_tables.table_name;
  21. PIPE ROW(l_tables_plus);
  22. /
  23.  
  24.  
  25. SELECT table_name,prev_table_name
  26. FROM TABLE(add_prev_table(CURSOR(SELECT * FROM all_tables)));

Report this snippet  

You need to login to post a comment.