In the below package I am getting error like
ORA-01010: invalid OCI operation
ORA-02063: preceding line from DB_LINK_SUITE_CRM
This is happening in the insert statement. I have highlighted the insert statement in bold italic
create or replace package body FIN_PKG_ACC_INS_UPD IS
m_id varchar2(30000);
m_id_c varchar2(30000);
PROCEDURE stp_ins_upd IS
m_seq_val number;
m_err_seq_num number;
m_err_msg varchar2(500);
BEGIN
IF C3%ISOPEN THEN
CLOSE C3;
END IF;
IF C2%ISOPEN THEN
CLOSE C2;
END IF;
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
select job_seq.nextval
into m_seq_val from dual;
dummy('stp_ins_upd ');
insert into om_job
(
job_sys_id,
job_name,
job_start_dts,
job_cr_dt,
job_cr_uid,
job_upd_date,
job_upd_uid
)
values
(
m_seq_val,
'odproc_update_holder_type',
to_char(systimestamp),
sysdate,
user,
sysdate,
user
);
open c1;
loop
begin
fetch c1 into m_c1;
exit when c1%notfound;
dummy('exit when c1 notfound ');
open c2;
fetch c2 into m_c2;
if c2%notfound THEN
dummy('stp_ins_upd ');
select "m_uuid"
into m_id_c
from "vw_uuid"@db_link_suite_crm ;
dummy('m_id_c '||m_id_c);
STP_INS_ACCOUNTS_CSTM;
else
STP_UPD_ACCOUNTS_CSTM;
end if;
open c3;
fetch c3 into m_c3;
if c3%notfound THEN
STP_INS_ACCOUNTS;
ELSE
STP_UPD_ACCOUNTS;
END IF;
close c3;
close c2;
exception
when others then
select job_seq.nextval
into m_err_seq_num from dual;
m_err_msg := sqlerrm;
insert into account_error_log
(
err_id,
job_id,
err_desc,
cstcode,
shortname,
crdate,
upddate,
updid,
crid,
custname ,
analy08,
analy01,
analy03,
billphn_mob,
billaddr1,
billcity,
billpostcd,
billcntcd,
crdlmt,
telno,
mobno,
shipphn_mob,
shpaddr1,
shipcity,
shipstat,
shippost,
shipcountry,
custanaly1,
custanaly2,
custanaly3,
custanaly4,
custanaly5,
custanaly6,
custanaly7,
custanaly8,
custanaly9,
custanaly10,
custanaly11,
custanaly12,
custanaly13,
monthsal ,
currcode
)
values
(
m_err_seq_num,
m_seq_val,
m_err_msg,
m_c1.cstcode,
m_c1.shortname,
m_c1.crdate,
m_c1.upddate,
m_c1.updid,
m_c1.crid,
m_c1.custname ,
m_c1.analy08,
m_c1.analy01,
m_c1.analy03,
m_c1.billphn_mob,
m_c1.billaddr1,
m_c1.billcity,
m_c1.billpostcd,
m_c1.billcntcd,
m_c1.crdlmt,
m_c1.telno,
m_c1.mobno,
m_c1.shipphn_mob,
m_c1.shpaddr1,
m_c1.shipcity,
m_c1.shipstat,
m_c1.shippost,
m_c1.shipcountry,
m_c1.custanaly1,
m_c1.custanaly2,
m_c1.custanaly3,
m_c1.custanaly4,
m_c1.custanaly5,
m_c1.custanaly6,
m_c1.custanaly7,
m_c1.custanaly8,
m_c1.custanaly9,
m_c1.custanaly10,
m_c1.custanaly11,
m_c1.custanaly12,
m_c1.custanaly13,
m_c1.monthsal ,
m_c1.currcode
);
end;
end loop;
close c1;
update om_job
set job_end_dts = to_char(systimestamp),
job_upd_date =sysdate,
job_upd_uid = user
where job_sys_id = m_seq_val;
commit;
exception
when others then
rollback;
END stp_ins_upd;
PROCEDURE STP_INS_UPD_CRT_DATE(P_CR_DT DATE) IS
BEGIN
NULL;
END STP_INS_UPD_CRT_DATE;
PROCEDURE STP_INS_UPD_UPD_DATE(P_UPD_DT DATE) IS
BEGIN
NULL;
END STP_INS_UPD_UPD_DATE;
PROCEDURE STP_INS_ACCOUNTS_CSTM IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
dummy('STP_INS_ACCOUNTS_CSTM ');
dummy('m_uuid '||m_id_c);
insert into "accounts_cstm"@db_link_suite_crm
(
"id_c",
"cust_anly_code_01_c",
"cust_anly_code_02_c",
"cust_anly_code_03_c",
"cust_anly_code_04_c",
"cust_anly_code_05_c",
"cust_anly_code_06_c",
"cust_anly_code_07_c",
"cust_anly_code_08_c",
"cust_anly_code_09_c",
"cust_anly_code_10_c",
"cust_anly_code_11_c",
"cust_anly_code_12_c",
"cust_anly_code_13_c",
"monthly_revenue_c",
"currency_id",
"accountnumber_c"
)
values(m_id_c,
m_c1.custanaly1,
m_c1.custanaly2,
m_c1.custanaly3,
m_c1.custanaly4,
m_c1.custanaly5,
m_c1.custanaly6,
m_c1.custanaly7,
m_c1.custanaly8,
m_c1.custanaly9,
m_c1.custanaly10,
m_c1.custanaly11,
m_c1.custanaly12,
m_c1.custanaly13,
m_c1.monthsal,
m_c1.currcode,
m_c1.cstcode
);
dummy('after insert accounts_cstm ');
commit;
END STP_INS_ACCOUNTS_CSTM;
PROCEDURE STP_UPD_ACCOUNTS_CSTM IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
update "accounts_cstm"@db_link_suite_crm
set "cust_anly_code_01_c" = m_c1.custanaly1,
"cust_anly_code_02_c" = m_c1.custanaly2,
"cust_anly_code_03_c" = m_c1.custanaly3,
"cust_anly_code_04_c" = m_c1.custanaly4,
"cust_anly_code_05_c" = m_c1.custanaly5,
"cust_anly_code_06_c" = m_c1.custanaly6,
"cust_anly_code_07_c" = m_c1.custanaly7,
"cust_anly_code_08_c" = m_c1.custanaly8,
"cust_anly_code_09_c" = m_c1.custanaly9,
"cust_anly_code_10_c" = m_c1.custanaly10,
"cust_anly_code_11_c" = m_c1.custanaly11,
"cust_anly_code_12_c" = m_c1.custanaly12,
"cust_anly_code_13_c" = m_c1.custanaly13,
"monthly_revenue_c" = m_c1.monthsal,
"currency_id" = m_c1.currcode
where "accountnumber_c" = m_c1.cstcode;
commit;
END STP_UPD_ACCOUNTS_CSTM;
PROCEDURE STP_INS_ACCOUNTS IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into "accounts"@db_link_suite_crm
( "id",
"name",
"date_entered",
"date_modified",
"modified_user_id",
"created_by",
--"description",
"account_type",
"industry",
"annual_revenue",
"phone_fax",
"billing_address_street",
"billing_address_city",
"billing_address_state",
"billing_address_postalcode",
"billing_address_country",
"rating",
"phone_office",
"phone_alternate",
"shipping_address_street",
"shipping_address_city",
"shipping_address_state",
"shipping_address_postalcode",
"shipping_address_country"
)
values
( m_id_c,
m_c1.shortname,
m_c1.crdate,
m_c1.upddate,
m_c1.updid,
m_c1.crid,
--m_c1.custname,
m_c1.analy08,
m_c1.analy01,
m_c1.analy03,
m_c1.billphn_mob,
m_c1.billaddr1,
m_c1.billcity,
m_c1.billstat,
m_c1.billpostcd,
m_c1.billcntcd,
m_c1.crdlmt,
m_c1.telno,
m_c1.mobno,
m_c1.shpaddr1,
m_c1.shipcity,
m_c1.shipstat,
m_c1.shippost,
m_c1.shipcountry
);
commit;
END STP_INS_ACCOUNTS;
PROCEDURE STP_UPD_ACCOUNTS IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
update "accounts"@db_link_suite_crm
set "name" = m_c1.shortname,
"date_entered" = m_c1.crdate,
"date_modified" = m_c1.upddate,
"modified_user_id" = m_c1.updid,
"created_by" = m_c1.crid,
--"description" = m_c1.custname,
"account_type" = m_c1.analy08,
"industry" = m_c1.analy01,
"annual_revenue" = m_c1.analy03,
"phone_fax" = m_c1.billphn_mob,
"billing_address_street" = m_c1.billaddr1,
"billing_address_city" = m_c1.billcity,
"billing_address_state" = m_c1.billstat,
"billing_address_postalcode" = m_c1.billpostcd,
"billing_address_country" = m_c1.billcntcd,
"rating" = m_c1.crdlmt,
"phone_office" = m_c1.telno,
"phone_alternate" = m_c1.mobno,
"shipping_address_street" = m_c1.shpaddr1,
"shipping_address_city" = m_c1.shipcity,
"shipping_address_state" = m_c1.shipstat,
"shipping_address_postalcode" = m_c1.shippost,
"shipping_address_country"= m_c1.shipcountry
where "id" = m_id ;
commit;
END STP_UPD_ACCOUNTS;
END FIN_PKG_ACC_INS_UPD;
/