SQL & PL/SQL

Hello Guys,

I am trying to do bulk insert into a table. Below is the script I am running:

--Object Type
create or replace type OPN_DLVRY_SCALARTYP as object(ACCRD_INTRST NUMBER(21,7),
AGGREGATION_REQD NUMBER(9,0),
AGGR_DLVRY_IND NUMBER(1,0),
ALCTN_INSTNC NUMBER(9,0),
BOOKING_TYPE NUMBER(2,0),
CASH_ACNT_NUM NUMBER(9,0),
CASH_ACNT_TYPE NUMBER(4,0),
CASH_ACNT_TYPE_CD NUMBER(12,0),
CHRGS_ACNT_NUM NUMBER(9,0),
CHRGS_ACNT_TYPE NUMBER(4,0),
CHRGS_ACNT_TYPE_CD NUMBER(12,0),
CLAIM_REF NUMBER(9,0),
CNTR_PRTY_ADRS_1 VARCHAR2(35 CHAR),
CNTR_PRTY_ADRS_2 VARCHAR2(35 CHAR),
CNTR_PRTY_ADRS_3 VARCHAR2(35 CHAR),
CNTR_PRTY_ID NUMBER(9,0),
CNTR_PRTY_NAME VARCHAR2(35 CHAR),
CNTR_PRTY_PRTCPNT_ID VARCHAR2(35 CHAR),
CORR_STLMNT_ADVC_ID NUMBER(9,0),
CRNCY VARCHAR2(3 CHAR),
CROSS_INDCTR NUMBER(1,0),
CSTDN_ACNT_NUM NUMBER(8,0),
CSTDN_ID NUMBER(9,0),
CSTDN_ORDR_ID VARCHAR2(22 CHAR),
CS_CSH_BMDL NUMBER(2,0),
CS_CSH_BMDL_CD NUMBER(12,0),
CS_CSH_BMTHD NUMBER(2,0),
CS_CSH_BMTHD_CD NUMBER(12,0),
CS_SEC_BMDL NUMBER(2,0),
CS_SEC_BMDL_CD NUMBER(12,0),
CS_SEC_BMTHD NUMBER(2,0),
CS_SEC_BMTHD_CD NUMBER(12,0),
DEAL_PRICE NUMBER(21,7),
DISCOUNT NUMBER(21,7),
DI_ID NUMBER(9,0),
DM_BTID NUMBER(9,0),
DM_BTNUMBER NUMBER(10,0),
DM_LSTUPDDT DATE,
DM_USERID NUMBER(9,0),
DM_WSID NUMBER(4,0),
DT_ID NUMBER(9,0),
EXT_FLAG NUMBER(1,0),
EXT_NAME VARCHAR2(35 CHAR),
EXT_TXN_NO NUMBER(9,0),
GEN_MSG_FLAG NUMBER(1,0),
INCLUDE_FLAG NUMBER(1,0),
INSTRMNT_ID NUMBER(8,0),
LNKD_DLVRY_ID NUMBER(9,0),
MARKET NUMBER(8,0),
MARKET_CD NUMBER(12,0),
MIR_FLAG NUMBER(1,0),
MRKT VARCHAR2(2 CHAR),
MSG_SEME VARCHAR2(16 CHAR),
NET_STLMNT_FLAG NUMBER(2,0),
NO_OF_ACCRL_DAYS NUMBER(6,0),
OPEN_DELIVERY_VER NUMBER(4,0),
OPEN_DLVRY_ID NUMBER(9,0),
OPEN_DLVRY_STAT NUMBER(2,0),
OPEN_DLVRY_STAT_CD NUMBER(12,0),
OPEN_DLVRY_TYPE NUMBER(2,0),
OPEN_DLVRY_TYPE_CD NUMBER(12,0),
ORDR_DEAL_POS_ID NUMBER(9,0),
ORDR_DEAL_POS_TYPE NUMBER(4,0),
ORDR_DEAL_POS_TYPE_CD NUMBER(12,0),
ORDR_TYPE NUMBER(2,0),
OU_ID VARCHAR2(15 CHAR),
PAYMENT_DATE DATE,
PAYMENT_ID VARCHAR2(15 CHAR),
POSK NUMBER(9,0),
POS_TYPE NUMBER(2,0),
POS_TYPE_CD NUMBER(12,0),
PRE_MTCHNG_REASON NUMBER(3,0),
PRE_MTCHNG_STAT NUMBER(2,0),
PRE_MTCHNG_STAT_CD NUMBER(12,0),
PRTL_STLMNT NUMBER(2,0),
PRTL_STLMNT_CD NUMBER(12,0),
RLTD_REF VARCHAR2(22 CHAR),
RMRKS VARCHAR2(35 CHAR),
SEC_AMT_QTY NUMBER(21,7),
SE_EXEC_ID VARCHAR2(15 CHAR),
SE_NET_STLMNT_ID NUMBER(9,0),
SE_PRTCPNT_ID VARCHAR2(15 CHAR),
SS_CSH_BMDL NUMBER(2,0),
SS_CSH_BMDL_CD NUMBER(12,0),
SS_CSH_BMTHD NUMBER(2,0),
SS_CSH_BMTHD_CD NUMBER(12,0),
SS_SEC_BMDL NUMBER(2,0),
SS_SEC_BMDL_CD NUMBER(12,0),
SS_SEC_BMTHD NUMBER(2,0),
SS_SEC_BMTHD_CD NUMBER(12,0),
STCK_EXCH_ID NUMBER(9,0),
STLMNT_ADVC_DT DATE,
STLMNT_ADVC_ID NUMBER(6,0),
STLMNT_AMT NUMBER(21,7),
STLMNT_CRNCY VARCHAR2(3 CHAR),
STLMNT_DT DATE,
STLMNT_MODEL NUMBER(2,0),
STLMNT_MODEL_CD NUMBER(12,0),
STLMNT_TYPE NUMBER(3,0),
STLMNT_TYPE_CD NUMBER(12,0),
SUB_POS_NUM NUMBER(9,0),
SUB_POS_TYPE NUMBER(4,0),
TAX_APPLICABLE NUMBER(1,0),
TRD_DT DATE,
TXN_DT DATE,
TXN_DTL VARCHAR2(135 CHAR),
UNIT NUMBER(1,0),
UNIT_CD NUMBER(12,0),
UNMATCHED_REASON VARCHAR2(35 CHAR),
YIELD NUMBER(21,7))
/

CREATE OR REPLACE TYPE OPN_DLVRY_ARRAYTYP AS TABLE OF OPN_DLVRY_SCALARTYP
/

----------------------------------------------------------------------------------
--Anonymus Block
DECLARE
ERR_NUM NUMBER;
ERR_MSG VARCHAR2(2000 CHAR);
V_ROW_COUNT NUMBER;
V_ROW_COUNT_T NUMBER DEFAULT 0;

cursor c is SELECT OPN_DLVRY_SCALARTYP(ACCRD_INTRST,
AGGR_DLVRY_IND,
AGGREGATION_REQD,
ALCTN_INSTNC,
BOOKING_TYPE,
CASH_ACNT_NUM,
CASH_ACNT_TYPE,
CASH_ACNT_TYPE_CD,
CHRGS_ACNT_NUM,
CHRGS_ACNT_TYPE,
CHRGS_ACNT_TYPE_CD,
CLAIM_REF,
CNTR_PRTY_ADRS_1,
CNTR_PRTY_ADRS_2,
CNTR_PRTY_ADRS_3,
CNTR_PRTY_ID,
CNTR_PRTY_NAME,
CNTR_PRTY_PRTCPNT_ID,
CORR_STLMNT_ADVC_ID,
CRNCY,
CROSS_INDCTR,
CS_CSH_BMDL,
CS_CSH_BMDL_CD,
CS_CSH_BMTHD,
CS_CSH_BMTHD_CD,
CS_SEC_BMDL,
CS_SEC_BMDL_CD,
CS_SEC_BMTHD,
CS_SEC_BMTHD_CD,
CSTDN_ACNT_NUM,
CSTDN_ID,
CSTDN_ORDR_ID,
DEAL_PRICE,
DI_ID,
DISCOUNT,
DM_BTID,
DM_BTNUMBER,
DM_LSTUPDDT,
DM_USERID,
DM_WSID,
DT_ID,
EXT_FLAG,
EXT_NAME,
EXT_TXN_NO,
GEN_MSG_FLAG,
INCLUDE_FLAG,
INSTRMNT_ID,
LNKD_DLVRY_ID,
MARKET,
MARKET_CD,
MIR_FLAG,
MRKT,
MSG_SEME,
NET_STLMNT_FLAG,
NO_OF_ACCRL_DAYS,
OPEN_DELIVERY_VER,
OPEN_DLVRY_ID,
OPEN_DLVRY_STAT,
OPEN_DLVRY_STAT_CD,
OPEN_DLVRY_TYPE,
OPEN_DLVRY_TYPE_CD,
ORDR_DEAL_POS_ID,
ORDR_DEAL_POS_TYPE,
ORDR_DEAL_POS_TYPE_CD,
ORDR_TYPE,
OU_ID,
PAYMENT_DATE,
PAYMENT_ID,
POS_TYPE,
POS_TYPE_CD,
POSK,
PRE_MTCHNG_REASON,
PRE_MTCHNG_STAT,
PRE_MTCHNG_STAT_CD,
PRTL_STLMNT,
PRTL_STLMNT_CD,
RLTD_REF,
RMRKS,
SE_EXEC_ID,
SE_NET_STLMNT_ID,
SE_PRTCPNT_ID,
SEC_AMT_QTY,
SS_CSH_BMDL,
SS_CSH_BMDL_CD,
SS_CSH_BMTHD,
SS_CSH_BMTHD_CD,
SS_SEC_BMDL,
SS_SEC_BMDL_CD,
SS_SEC_BMTHD,
SS_SEC_BMTHD_CD,
STCK_EXCH_ID,
STLMNT_ADVC_DT,
STLMNT_ADVC_ID,
STLMNT_AMT,
STLMNT_CRNCY,
STLMNT_DT,
STLMNT_MODEL,
STLMNT_MODEL_CD,
STLMNT_TYPE,
STLMNT_TYPE_CD,
SUB_POS_NUM,
SUB_POS_TYPE,
TAX_APPLICABLE,
TRD_DT,
TXN_DT,
TXN_DTL,
UNIT,
UNIT_CD,
UNMATCHED_REASON,
YIELD
) FROM
(SELECT SRC1.ALCTN_INSTNC ACCRD_INTRST,
NULL AGGR_DLVRY_IND,
NULL AGGREGATION_REQD,
NULL ALCTN_INSTNC,
DECODE(BSRC1.DEAL_POS_TYPE,64,2) BOOKING_TYPE,
SRC1.CASH_ACNT_NUM CASH_ACNT_NUM,
MigD_ChangedSysCode(5290000,SRC1.CASH_ACNT_TYPE) CASH_ACNT_TYPE,
5290000 CASH_ACNT_TYPE_CD,
SRC1.CHRGS_ACNT_NUM CHRGS_ACNT_NUM,
MigD_ChangedSysCode(5290000,SRC1.CHRGS_ACNT_TYPE) CHRGS_ACNT_TYPE,
5290000 CHRGS_ACNT_TYPE_CD,
NULL CLAIM_REF,
SRC1.CNTR_PRTY_ADRS_1 CNTR_PRTY_ADRS_1,
SRC1.CNTR_PRTY_ADRS_2 CNTR_PRTY_ADRS_2,
SRC1.CNTR_PRTY_ADRS_3 CNTR_PRTY_ADRS_3,
SRC1.CNTR_PRTY_ID CNTR_PRTY_ID,
SRC1.CNTR_PRTY_NAME CNTR_PRTY_NAME,
SRC1.CNTR_PRTY_PRTCPNT_ID CNTR_PRTY_PRTCPNT_ID,
SRC1.CORR_STLMNT_ADVC_ID CORR_STLMNT_ADVC_ID,
(SELECT DISTINCT BSRC3.DEAL_CRNCY FROM ST_DEAL BSRC3 WHERE BSRC3.DEAL_POS_TYPE = 64 AND BSRC3.DEAL_ID = BSRC1.DEAL_ID AND BSRC3.DEAL_POS_TYPE = BSRC1.DEAL_POS_TYPE) CRNCY,
SRC1.CROSS_INDCTR CROSS_INDCTR,
1 CS_CSH_BMDL,
15690000 CS_CSH_BMDL_CD,
2 CS_CSH_BMTHD,
10180000 CS_CSH_BMTHD_CD,
1 CS_SEC_BMDL,
15690000 CS_SEC_BMDL_CD,
2 CS_SEC_BMTHD,
10180000 CS_SEC_BMTHD_CD,
NULL CSTDN_ACNT_NUM,
SRC1.CSTDN_ID CSTDN_ID,
SRC1.CSTDN_ORDR_ID CSTDN_ORDR_ID,
NULL DEAL_PRICE,
NULL DI_ID,
NULL DISCOUNT,
1 DM_BTID,
1 DM_BTNUMBER,
SRC1.DM_LSTUPDDT DM_LSTUPDDT,
1 DM_USERID,
1 DM_WSID,
NULL DT_ID,
NULL EXT_FLAG,
NULL EXT_NAME,
NULL EXT_TXN_NO,
2 GEN_MSG_FLAG,
SRC1.INCLUDE_FLAG INCLUDE_FLAG,
SRC1.INSTRMNT_ID INSTRMNT_ID,
NULL LNKD_DLVRY_ID,
NULL MARKET,
13620000 MARKET_CD,
NULL MIR_FLAG,
NULL MRKT,
NULL MSG_SEME,
SRC1.NET_STLMNT_FLAG NET_STLMNT_FLAG,
NULL NO_OF_ACCRL_DAYS,
SRC1.OPEN_DELIVERY_VER OPEN_DELIVERY_VER,
SRC1.OPEN_DLVRY_ID OPEN_DLVRY_ID,
MigD_ChangedSysCode(2650000,SRC1.OPEN_DLVRY_STAT) OPEN_DLVRY_STAT,
2650000 OPEN_DLVRY_STAT_CD,
MigD_ChangedSysCode(5700000,SRC1.OPEN_DLVRY_TYPE) OPEN_DLVRY_TYPE,
5700000 OPEN_DLVRY_TYPE_CD,
SRC1.ORDR_DEAL_POS_ID ORDR_DEAL_POS_ID,
MigD_ChangedSysCode(5290000,SRC1.ORDR_DEAL_POS_TYPE) ORDR_DEAL_POS_TYPE,
NULL ORDR_DEAL_POS_TYPE_CD,
SRC1.ORDR_TYPE ORDR_TYPE,
DECODE(SRC1.OU_ID,'RSK','GSLAL','SLI','GSLIL',SRC1.OU_ID) OU_ID,
NULL PAYMENT_DATE,
NULL PAYMENT_ID,
MigD_ChangedSysCode(5300000,SRC1.POS_TYPE) POS_TYPE,
5300000 POS_TYPE_CD,
NULL POSK,
NULL PRE_MTCHNG_REASON,
MigD_ChangedSysCode(4110000,SRC1.PRE_MTCHNG_STAT) PRE_MTCHNG_STAT,
4110000 PRE_MTCHNG_STAT_CD,
2 PRTL_STLMNT,
7290000 PRTL_STLMNT_CD,
SRC1.RLTD_REF RLTD_REF,
SRC1.RMRKS RMRKS,
SRC1.SE_EXEC_ID SE_EXEC_ID,
SRC1.SE_NET_STLMNT_ID SE_NET_STLMNT_ID,
SRC1.SE_PRTCPNT_ID SE_PRTCPNT_ID,
SRC1.SEC_AMT_QTY SEC_AMT_QTY,
1 SS_CSH_BMDL,
15690000 SS_CSH_BMDL_CD,
2 SS_CSH_BMTHD,
10180000 SS_CSH_BMTHD_CD,
1 SS_SEC_BMDL,
15690000 SS_SEC_BMDL_CD,
2 SS_SEC_BMTHD,
10180000 SS_SEC_BMTHD_CD,
SRC1.STCK_EXCH_ID STCK_EXCH_ID,
SRC1.STLMNT_ADVC_DT STLMNT_ADVC_DT,
SRC1.STLMNT_ADVC_ID STLMNT_ADVC_ID,
SRC1.STLMNT_AMT STLMNT_AMT,
SRC1.STLMNT_CRNCY STLMNT_CRNCY,
SRC1.STLMNT_DT STLMNT_DT,
3 STLMNT_MODEL,
25150000 STLMNT_MODEL_CD,
MigD_ChangedSysCode(6470000,SRC1.STLMNT_TYPE) STLMNT_TYPE,
6470000 STLMNT_TYPE_CD,
SRC1.SUB_POS_NUM SUB_POS_NUM,
SRC1.SUB_POS_TYPE SUB_POS_TYPE,
NULL TAX_APPLICABLE,
SRC1.TRD_DT TRD_DT,
SRC1.TXN_DT TXN_DT,
SRC1.TXN_DTL TXN_DTL,
MigD_ChangedSysCode(1170000,SRC1.UNIT) UNIT,
1170000 UNIT_CD,
NULL UNMATCHED_REASON,
NULL YIELD
FROM OPEN_DELIVERY@RSLAL SRC1, ST_DEAL BSRC1
WHERE SRC1.ORDR_DEAL_POS_ID = BSRC1.DEAL_ID
AND SRC1.ORDR_DEAL_POS_TYPE = BSRC1.DEAL_POS_TYPE
AND BSRC1.DEAL_POS_TYPE =64);

L_DATA OPN_DLVRY_ARRAYTYP;

begin

open c;
loop
L_DATA := OPN_DLVRY_ARRAYTYP();
FETCH C BULK COLLECT INTO L_DATA LIMIT 50000;
DBMS_OUTPUT.PUT_LINE( 'Fetched '||L_DATA.COUNT||' rows' );

insert into OPEN_DELIVERY ( ACCRD_INTRST,
AGGR_DLVRY_IND,
AGGREGATION_REQD,
ALCTN_INSTNC,
BOOKING_TYPE,
CASH_ACNT_NUM,
CASH_ACNT_TYPE,
CASH_ACNT_TYPE_CD,
CHRGS_ACNT_NUM,
CHRGS_ACNT_TYPE,
CHRGS_ACNT_TYPE_CD,
CLAIM_REF,
CNTR_PRTY_ADRS_1,
CNTR_PRTY_ADRS_2,
CNTR_PRTY_ADRS_3,
CNTR_PRTY_ID,
CNTR_PRTY_NAME,
CNTR_PRTY_PRTCPNT_ID,
CORR_STLMNT_ADVC_ID,
CRNCY,
CROSS_INDCTR,
CS_CSH_BMDL,
CS_CSH_BMDL_CD,
CS_CSH_BMTHD,
CS_CSH_BMTHD_CD,
CS_SEC_BMDL,
CS_SEC_BMDL_CD,
CS_SEC_BMTHD,
CS_SEC_BMTHD_CD,
CSTDN_ACNT_NUM,
CSTDN_ID,
CSTDN_ORDR_ID,
DEAL_PRICE,
DI_ID,
DISCOUNT,
DM_BTID,
DM_BTNUMBER,
DM_LSTUPDDT,
DM_USERID,
DM_WSID,
DT_ID,
EXT_FLAG,
EXT_NAME,
EXT_TXN_NO,
GEN_MSG_FLAG,
INCLUDE_FLAG,
INSTRMNT_ID,
LNKD_DLVRY_ID,
MARKET,
MARKET_CD,
MIR_FLAG,
MRKT,
MSG_SEME,
NET_STLMNT_FLAG,
NO_OF_ACCRL_DAYS,
OPEN_DELIVERY_VER,
OPEN_DLVRY_ID,
OPEN_DLVRY_STAT,
OPEN_DLVRY_STAT_CD,
OPEN_DLVRY_TYPE,
OPEN_DLVRY_TYPE_CD,
ORDR_DEAL_POS_ID,
ORDR_DEAL_POS_TYPE,
ORDR_DEAL_POS_TYPE_CD,
ORDR_TYPE,
OU_ID,
PAYMENT_DATE,
PAYMENT_ID,
POS_TYPE,
POS_TYPE_CD,
POSK,
PRE_MTCHNG_REASON,
PRE_MTCHNG_STAT,
PRE_MTCHNG_STAT_CD,
PRTL_STLMNT,
PRTL_STLMNT_CD,
RLTD_REF,
RMRKS,
SE_EXEC_ID,
SE_NET_STLMNT_ID,
SE_PRTCPNT_ID,
SEC_AMT_QTY,
SS_CSH_BMDL,
SS_CSH_BMDL_CD,
SS_CSH_BMTHD,
SS_CSH_BMTHD_CD,
SS_SEC_BMDL,
SS_SEC_BMDL_CD,
SS_SEC_BMTHD,
SS_SEC_BMTHD_CD,
STCK_EXCH_ID,
STLMNT_ADVC_DT,
STLMNT_ADVC_ID,
STLMNT_AMT,
STLMNT_CRNCY,
STLMNT_DT,
STLMNT_MODEL,
STLMNT_MODEL_CD,
STLMNT_TYPE,
STLMNT_TYPE_CD,
SUB_POS_NUM,
SUB_POS_TYPE,
TAX_APPLICABLE,
TRD_DT,
TXN_DT,
TXN_DTL,
UNIT,
UNIT_CD,
UNMATCHED_REASON,
YIELD
)
SELECT * FROM TABLE( CAST (L_DATA AS OPN_DLVRY_ARRAYTYP) );

V_ROW_COUNT := SQL%ROWCOUNT;
V_ROW_COUNT_T := V_ROW_COUNT_T + V_ROW_COUNT;
exit when c%notfound;
end loop;
CLOSE C;
COMMIT;
DBMS_OUTPUT.PUT_LINE( 'Total Number of rows Inserted: '||V_ROW_COUNT_T);
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('EXCEPTION ERR_MSG: '||ERR_MSG);
ROLLBACK;
END;
/


But when I run this script I get exception : 'ORA-01438: value larger than specified precision allowed for this column'.

I have checked in my source table as well as in inserting table, everything looks fine to me.

Could some one please help me know how to handle such exception, which could actually tell me for what column and what data it is throwing exception.

Thanks in advance.

regards
Abhinav