Solved

TRANSFERRING DATA BETWEEN TWO TABLES OF DIFFERENT STRUCTURE

Posted on 2010-11-26
5
470 Views
Last Modified: 2013-12-07
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
Comment
Question by:Ravi_Chintada
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34220836
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
 

Author Comment

by:Ravi_Chintada
ID: 34220847
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
 
LVL 4

Expert Comment

by:hqassap
ID: 34221609
Please send the error message.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34230027
I think I understand what you are trying to do.

Check out FORALL:

http://www.akadia.com/services/ora_bulk_insert.html
0
 
LVL 32

Expert Comment

by:awking00
ID: 34230086
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question