Thursday, 28 January 2016

PL/SQL Table Using Bulk Collect


/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
   TYPE amresh IS TABLE OF emp_test%ROWTYPE;

   objecttable   amresh;

   CURSOR cur
   IS
      SELECT *
        FROM emp_test;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur
      BULK COLLECT INTO objecttable;

      FORALL i IN 1 .. objecttable.COUNT
         INSERT INTO emp_test_t
              VALUES objecttable (i);
      EXIT WHEN cur%NOTFOUND;
   END LOOP;

   CLOSE cur;
END;




CREATE TABLE EMP_TEST
(
  EMP_ID     NUMBER,
  ORG_CODE   VARCHAR2(50 BYTE),
  ITEM_CODE  VARCHAR2(250 BYTE),
  E_NUMBER   VARCHAR2(100 BYTE)
)


CREATE TABLE EMP_TEST_T
(
  EMP_ID     NUMBER,
  ORG_CODE   VARCHAR2(50 BYTE),
  ITEM_CODE  VARCHAR2(250 BYTE),
  E_NUMBER   VARCHAR2(100 BYTE)
)



/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
   TYPE amresh IS TABLE OF emp_test%ROWTYPE;  -- THIS IS CALLED PL/SQL TABLE - OBJECT OF TYPE IS KNOW AS PL/SQL TABLE

   L_DATA   amresh;           ---- FROM TABLE IM GETTING DATA THROUGH AMRESH , FOR AMRESH IM DECLARING THE VARIABLE

   CURSOR cur
   IS
      SELECT *
        FROM emp_test;
BEGIN
   OPEN cur;

   LOOP
      FETCH cur
      BULK COLLECT INTO L_DATA;

      FORALL i IN 1 .. L_DATA.COUNT
         INSERT INTO emp_test_t
              VALUES L_DATA (i);
      EXIT WHEN cur%NOTFOUND;
   END LOOP;

   CLOSE cur;
END;

No comments:

Post a Comment