Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

TRANSFERRING DATA BETWEEN TWO TABLES OF DIFFERENT STRUCTURE

Hi All,

            I m trying to insert data into a table which consists of columns of different types from the table of same type.

            Can I use cursor with bulk collect option to do this?

Please help me.

Thanks & Regards,
Ravi
0
Ravi_Chintada
Asked:
Ravi_Chintada
1 Solution
 
slightwv (䄆 Netminder) Commented:
Maybe but do you need to?

Can you not do a simple insert into tabA ( select col from tabB)?

Please post some test tables, sample data and expected results.
0
 
Ravi_ChintadaAuthor Commented:
Hi
    Thanks for your response.

          Please find the following procedure that I m working around.

          Please suggest me what is the mistake in this procedure.
-----------------------------------------------------------------------------------
PROCEDURE BULK_COLLECT_INTO_MOBILE

   IS
 
  TYPE MOBILE_BULK_REC IS RECORD
       (R_MSISDN TO2_MOBILE_DETAIL.MSISDN%TYPE,
        R_ACTIVE  TO2_MOBILE_DETAIL.ACTIVE%TYPE,
        R_ICCID  TO2_MOBILE_DETAIL.ICCID%TYPE,
        R_CUSTOMER_ID TO2_MOBILE_DETAIL.CUSTOMER_ID%TYPE,
        R_IS_PREPAID  TO2_MOBILE_DETAIL.IS_PREPAID%TYPE,
        R_IMSI   TO2_MOBILE_DETAIL.IMSI%TYPE,
        R_CONTRACT_ID   TO2_MOBILE_DETAIL.CONTRACT_ID%TYPE,
      );
 
  TYPE MOBILE_BULK_TAB IS TABLE OF MOBILE_BULK_REC;    
       
  V_MOBILE_BULK      MOBILE_BULK_TAB;
  V_ARRAY_SIZE     NUMBER := 10000;
  V_TOT_CNT        NUMBER(20) :=0;
 
  CURSOR C1 IS
         SELECT
          MSISDN,
             ACTIVE,
             ICCID,
             CUSTOMER_ID,
             IS_PREPAID,
             IMSI,
             CONTRACT_ID
             
         FROM TO2_MOBILE_DETAIL_STG;
           
         
  PROCEDURE writelog(pMSG IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
 
  BEGIN -- writelog
    INSERT INTO POST_BULK_MOBILE_LOG
           VALUES ('BULK_COLLECT_INS_MOBILE','MAIN',pMsg, SYSDATE, 0);
    COMMIT;
  END writelog;


  BEGIN
    writelog('Started');
 
 

  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT
          INTO
              V_MOBILE_BULK LIMIT V_ARRAY_SIZE;

BEGIN
         
     INSERT INTO TO2_MOBILE_DETAIL
    (MSISDN,
             ACTIVE,
             ICCID,
             CUSTOMER_ID,
             IS_PREPAID,
             IMSI,
             CONTRACT_ID)
     SELECT    
             MSISDN,
            ACTIVE,
            ICCID,
            TO_NUMBER(CUSTOMER_ID),
            IS_PREPAID,
            IMSI,
            TO_NUMBER(CONTRACT_ID)
     FROM TO2_MOBILE_DETAIL_STG;
               
         
  EXIT WHEN V_MOBILE_BULK.COUNT=0;
     
  V_TOT_CNT:=V_TOT_CNT+V_MOBILE_BULK.COUNT;
 
  COMMIT;
 
  writelog('Inserted into MOBILE_BULK so far: '|| V_TOT_CNT);
 
  END LOOP;
 
  CLOSE C1;
 
  COMMIT;
 
  writelog('Finished - Total Rows: '|| V_TOT_CNT);
END LOOP;  

EXCEPTION
  WHEN OTHERS THEN
    writelog(SQLERRM);
END;
0
 
hqassapCommented:
Please send the error message.
0
 
slightwv (䄆 Netminder) Commented:
I think I understand what you are trying to do.

Check out FORALL:

http://www.akadia.com/services/ora_bulk_insert.html
0
 
awking00Commented:
What are the differences in structures of T02_MOBILE_DETAIL_STG and TO2_MOBILE_DETAIL?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now