ORA-12840: cannot access a remote table after parallel/insert direct load txn
Dear All,
while executing this procedure , I am getting this error.
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "DWHCO.GET_UNID_FROM_TABLE", line 17
ORA-06512: at line 1
ORA-06512: at "DWHCO.MIG_TEST", line 7
ORA-06512: at line 2
plz find the procedure
CREATE OR REPLACE PROCEDURE MIG_TEST AS
begin
commit;
Insert /*+ APPEND */ into TEST_V24
(
TEST_TXT_1,
TEST_TXT_2
)
select
get_unid_from_table('TB0_CURRENCY@dwhold','UNID','CURRENCY_CODE', 'TEST', NULL, NULL),
'jojan'
from TEST_V241;
commit;
end;
/
In this procedure I am calling a function. in that function I am taking records from another instance using DBLINK.
Plz find the function..
CREATE OR REPLACE function get_unid_from_table (p_table varchar2,p_unid varchar2, p_field1 varchar2, p_value1 varchar2, p_field2 varchar2, p_value2 varchar2)
return number
as
v_SQL varchar2(2000);
v_unid number;
begin
v_sql := 'select ' || p_unid || ' from ' || p_table || ' where ' || p_field1 || ' = ''' || trim(p_value1) || '''';
if p_field2 is not null and p_value2 is not null then
v_sql := v_sql || ' and ' || p_field2 || ' = ''' || trim(p_value2) || '''';
end if;
v_sql := v_sql || ' and rownum = 1';
begin
execute immediate v_sql into v_unid;
return v_unid;
--exception
--when others then
--return 0;
end;
If I remove this command /*+ APPEND */ from the procedure , then it works perfectly. using this I am getting this error message mentioned above. I Tried a lot to solve this issue but couldnot do the same. If we run the same procedure in the same instance itself(ie without using dblink)it will also work.
kindly provide a solution for the same..
jojan T A
