2 Replies Latest reply: Apr 13, 2014 10:48 AM by rp0428 RSS

    How to Copy data from one collection to another


      I am getting below error while trying to copy data from one collection to other.


      • Error(17,8): PL/SQL: ORA-00904: "COLUMN_VALUE": invalid identifier


      Please help me providing a better way.

      create or replace function scrub_final_2 return sys_refcursor IS

      x type_tbl;                                   /*create or replace type type_record as object(employee_id NUMBER(6), first_name VARCHAR2(20));*/

      test1 type_tbl;                              /*create or replace type type_tbl as table of type_record;*/

      y sys_refcursor;

      z sys_refcursor;





      z:=scrub_final_1;       /*This is a function which returns a refcursor*/


      fetch z bulk collect into test1;

      exit when z%NOTFOUND;


      select column_value bulk collect into x from  table(test1) ;

      end loop;

      open y for select employee_id,first_name from employees a where not exists (select employee_id from table(x) where a.employee_id=employee_id);

      return y;