set echo on
set feedback on

-- Version Number
SELECT * FROM v$version;

-- Drop Tables and Types
DROP TABLE t1;
DROP TABLE t2;
DROP TYPE typ_tbl;
DROP TYPE typ_obj;
DROP PACKAGE testpkg;

-- Create Types
CREATE OR REPLACE TYPE typ_obj IS OBJECT
(
  C1  VARCHAR2(255),
  C2  VARCHAR2(255)
)
/

CREATE OR REPLACE TYPE typ_tbl IS TABLE OF typ_obj
/

-- Create Source Table
CREATE TABLE t1
(
  T1_C1  VARCHAR2(255),
  T1_C2  VARCHAR2(255)
);

INSERT INTO t1
SELECT object_name || rownum t1_c1, object_type t1_c2
FROM   dba_objects
WHERE  rownum < 10000;

COMMIT;

-- Create Desyination Table
CREATE TABLE t2
(
  T2_C1  VARCHAR2(255),
  T2_C2  VARCHAR2(255)
);

-- Create Unique Index On Destination Table
CREATE UNIQUE INDEX t2_idx ON t2(t2_c1);

-- Create Package Specification
CREATE OR REPLACE PACKAGE testpkg
IS
   TYPE typ_rec IS RECORD
      (
       C1   VARCHAR2(255),
       C2   VARCHAR2(255)
      );

   TYPE typ_refcur IS REF CURSOR RETURN typ_rec;

   FUNCTION loaddata
              (p_refcur typ_refcur)
   RETURN   typ_tbl
   PIPELINED
   PARALLEL_ENABLE(PARTITION p_refcur BY ANY);

END testpkg;
/

-- Create Package Body
CREATE OR REPLACE PACKAGE BODY testpkg
IS
   FUNCTION loaddata
              (p_refcur typ_refcur)
   RETURN   typ_tbl
   PIPELINED
   PARALLEL_ENABLE(PARTITION p_refcur BY ANY)
   IS
      v_c1   VARCHAR2(255);
      v_c2   VARCHAR2(255);
      v_tbl  typ_tbl;

      PRAGMA AUTONOMOUS_TRANSACTION;

   BEGIN
      LOOP
         FETCH p_refcur INTO v_c1, v_c2;
           EXIT WHEN p_refcur%NOTFOUND;

         MERGE INTO t2
         USING (
                SELECT v_c1 c1, v_c2 c2
                FROM   dual
                ) t
         ON   (
               t2.t2_c1 = t.c1
               )
         WHEN MATCHED THEN
            UPDATE
               SET  t2.t2_c2 = t.c2
         WHEN NOT MATCHED THEN
            INSERT (
                    t2.t2_c1,
                    t2.t2_c2
                    )
            VALUES
                   (
                    t.c1,
                    t.c2
                    );
--      INSERT INTO t2 VALUES (v_c1, v_c2);
      COMMIT;

      END LOOP;
      CLOSE p_refcur;
      RETURN;
   END;
END testpkg;
/


-- Load Data
SELECT *
FROM   TABLE(testpkg.loaddata
          (CURSOR
             (
              SELECT /*+ parallel(t1) */ t1_c1, t1_c2
              FROM   t1
            )
         )
      );