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

    How to Copy data from one collection to another

    3050d5ea-07a7-4d00-ba74-013e3b861a69

      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;

      begin

       

       

      x:=type_tbl();

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

      loop

      fetch z bulk collect into test1;

      exit when z%NOTFOUND;

      x.extend;

      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;

      end;