DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Ora-12840

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    Ora-12840

    Getting this error message when trying to access a table via Oracle pipeline functions:

    ERROR at line 44:
    ORA-20004: ORA-20001: ORA-12840: cannot access a remote table after parallel/insert direct load txn
    ORA-06512: at "OPO.OPO_PKG_OUTBOUND_INTERFACE", line 3351
    ORA-06512: at line 1

    As fas as I can tell, this seems to be caused some sort of uncommitted transaction in a parallel DML operation. What I don't know is how to find out what the transaction is or how to commit it or rollback. This is an urgent production problem, so any help would be appreciated.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have many options

    if you have access to the remote database then check the the remote sessions using v$dblink, v$session

    if you wanna check from local database then check v$transaction

    what I dont know if you do the insert remotely?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by Scorby
    Getting this error message when trying to access a table via Oracle pipeline functions:

    ERROR at line 44:
    ORA-20004: ORA-20001: ORA-12840: cannot access a remote table after parallel/insert direct load txn
    ORA-06512: at "OPO.OPO_PKG_OUTBOUND_INTERFACE", line 3351
    ORA-06512: at line 1

    As fas as I can tell, this seems to be caused some sort of uncommitted transaction in a parallel DML operation. What I don't know is how to find out what the transaction is or how to commit it or rollback. This is an urgent production problem, so any help would be appreciated.
    Yes, You are right. After Parallel DML operation, you need to commit the transaction immediatley, otherwise you will get this error.

    Tamil

  4. #4
    Join Date
    Nov 2006
    Posts
    1

    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

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Insert /*+ APPEND */ into TEST_V24
    causes the problem
    You can't do that.
    Do it without the APPEND hint and it should work.
    This is a direct load-insert limitation as the previous post said

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width