Ravi_Chintada
asked on
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
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
ASKER
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%T YPE,
R_ACTIVE TO2_MOBILE_DETAIL.ACTIVE%T YPE,
R_ICCID TO2_MOBILE_DETAIL.ICCID%TY PE,
R_CUSTOMER_ID TO2_MOBILE_DETAIL.CUSTOMER _ID%TYPE,
R_IS_PREPAID TO2_MOBILE_DETAIL.IS_PREPA ID%TYPE,
R_IMSI TO2_MOBILE_DETAIL.IMSI%TYP E,
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',pM sg, 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_MOB ILE_BULK.C OUNT;
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;
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%T
R_ACTIVE TO2_MOBILE_DETAIL.ACTIVE%T
R_ICCID TO2_MOBILE_DETAIL.ICCID%TY
R_CUSTOMER_ID TO2_MOBILE_DETAIL.CUSTOMER
R_IS_PREPAID TO2_MOBILE_DETAIL.IS_PREPA
R_IMSI TO2_MOBILE_DETAIL.IMSI%TYP
R_CONTRACT_ID TO2_MOBILE_DETAIL.CONTRACT
);
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'
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_MOB
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;
Please send the error message.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What are the differences in structures of T02_MOBILE_DETAIL_STG and TO2_MOBILE_DETAIL?
Can you not do a simple insert into tabA ( select col from tabB)?
Please post some test tables, sample data and expected results.