Thanks for the question, Satish Mahant.

Asked: March 15, 2016 - 7:38 pm UTC

Last updated: March 17, 2016 - 2:21 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Issue:

we have two databases and we are trying to insert data from one of the databases and the DML while inserting from local table to remote table is throwing error. Please find the details.

Local Database: SV2INQA
Remote Database: SV2COMQ
Procedure Name: PROC1
Database link pointing from SV2INQA to SV2COMQ : link_cmn
Sequence name in SV2COMQ: B_seq

The issue is in statement written in PROC1

BEGIN
INSERT INTO TAB1@link_cmn
(A,B,C,D)
SELECT A,B_seq@link_cmn.nextval,C,D
FROM
TAB1;
EXCEPTION
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;

Error: ORA-02069: global_names parameter must be set to TRUE for this operation

Now, the same logic is implemented in other procedures. It is not working in one
of the procedures only. I implemented one solution . I created one local table as

CREATE TABLE TAB2
AS
SELECT * FROM TAB1 WHERE 1=2;

After that,

BEGIN
INSERT INTO TAB2
SELECT A,B,C,D FROM TAB1;
END;

BEGIN
INSERT INTO TAB1@link_cmn
(A,B,C,D)
SELECT A,B_seq@link_cmn,C,D
FROM
TAB2;
EXCEPTION
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;

It resolved the issue for somedays . But, the error suddenly comes out once again after few days with the above mentioned implementation.

I don't want to set Gloabl_names='TRUE' as the same db_link is working fine other procedures and the same kind of logic is written in every other procedures.

Please suggest how to resolve the issue.

Thanks in advance.

and Connor said...

From MOS Note 1047673.6, ORA-02069 DURING REMOTE INSERT OF A LOCAL SEQUENCE

-----------------------------------------
SYMPTOMS

You receive an ORA-02069 error during an insert to a remote database through a database link when the insert contained a local sequence. ORA-02069: global_names parameter must be set to TRUE for this operation

CAUSE

A remote mapping of the statement is required but cannot be achieved because global_names should be set to TRUE for it to be achieved.

SOLUTION

Problem Explanation:

====================

This is expected behaviour.

The insert statement is transformed so that it can be executed at the remote end of the dblink. The reference to the local sequence has to be qualified so that the remote end knows to call back to the local instance. The qualification is made by appending @local_dbname.domain to the sequence reference if global_names=true. When global_names=false, the code cannot make the assumption that the qualifier will be valid and reports the error ORA-02069 'global_names parameter must be set to TRUE for this operation'.

If you cannot set global_names = true, then use the following workaround:

1. Create a 'temporary' table on the local database for holding the inserts.
2. Insert the entire row into this table.
3. Insert the 'temporary' row from the temporary table to the remote table.
4. Delete the 'temporary' row from the temporary table. This is slower but it will get around the problem.

--OR-- Set GLOBAL_NAMES=TRUE in the init<SID>.ora

Solution Explanation:
=====================
The first workaround bypasses the remote node having to make a call back to the local instance to reference the local sequence.

-----------------------------------------

Hope this helps.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The issue still persists

Satish Mahant, March 16, 2016 - 6:50 pm UTC

Hi,

I understad what u said.Please find my points below:
1. The sequence is not local sequence.It is remote sequence.
2.The workaround which u r saying actually worked in my DEV but not in my QA.
3. The main problem is that the sam kind of cide is written in 8-9 procedures and all are working fine. Only one of the procedure is throwing error????
Why??.

The only thing that table TAB1 is that the data is inserted in that table whenever a trigger named trg is fired.Thag is the only difference between this procedure and other procedures.

Please help.

March 17, 2016 - 2:21 am UTC

Please send us a complete test case with the objects to be created on each database, and we'll try reproduce here.

Try removing the NEXTVAL from the INSERT

Martin Millage, February 07, 2017 - 11:41 pm UTC

I had the same problem. Running an insert to a remote table with a remote sequence threw ORA-02069. Setting global_values to TRUE is not an option because the dblink is not the same name as the database.

I put the insert into a PL/SQL block, but selected the nextval into a variable with a simple select from dual. See if this works for you:

DECLARE
    nNextVal NUMBER;
BEGIN
    SELECT sequenceName.NEXTVAL
      INTO nNextVal
      FROM dual;

    INSERT INTO tableName
        (col1,
            .
            .
            .
         idCol)
    VALUES
        (col1Val,
            .
            .
            .
         nNextVal);
END;

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

The issue still persists

Try removing the NEXTVAL from the INSERT

More to Explore

PL/SQL demos

PL/SQL docs

Issue in using DB link

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

The issue still persists

Try removing the NEXTVAL from the INSERT

More to Explore

PL/SQL demos

PL/SQL docs