Oracle stored procedure error ora-02069:global_names parameter must bes set to TRUE for this operation

Today, developed to send a message that a stored procedure would like to insert data through Dblink to the target library, but error ora-02069:global_names parameter must is set to TRUE for this operation, Want me to modify the Global_names parameter to true based on the error prompt on the database.

Let's take a look at how the official documentation describes this parameter:

Global_namesSpecifies whether a database link is required to the same name asThe database to which it connects.
If The value of Global_names is falseand then no check is performed. If you use the or plan to use distributed processing and then the Oracle recommends that you set this parameter to true t o ensure the use of consistent naming conventions for databases and links in a networked environment.

You can see from the following query that the Global_names parameter can be modified online.

[email protected]>col name for a30
[email protected]>select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name=‘global_names‘;

NAME			       ISSES_MODIFIABL ISSYS_MODIFIABLE
------------------------------ --------------- ---------------------------
global_names		       TRUE	       IMMEDIATE

After reading this parameter, take a look at the development of the stored procedure Code, where the INSERT statement used a sequence, so that caused the error. A simple stored procedure has been created on the test database to simulate this problem

Create a Dblink

[email protected]>create database link link_orcl connect to zx identified by "zx" using ‘orcl‘;

Database link created.

[email protected]>select * from [email protected]_orcl;

DUM
---
X

Create a stored procedure with a remote insert without a sequence first

[email protected]>create or replace procedure pro_a as
  2  begin
  3  insert into [email protected]_orcl (c1) values(‘a‘);
  4  commit;
  5  end;
  6  /

Procedure created.

Execute this stored procedure, observe the result, the data can be inserted normally

[email protected]>select * from [email protected]_orcl;

no rows selected

[email protected]>exec pro_a;

PL/SQL procedure successfully completed.

[email protected]>select c1 from [email protected]_orcl;

C1
---
a

Create a sequence and modify the stored procedure above

[email protected]>create sequence seq_a;

Sequence created.

[email protected]>create or replace procedure pro_a as
  2  begin
  3  insert into [email protected]_orcl (c1,n1) values(‘a‘,seq_a.nextval);
  4  commit;
  5  end;
  6  /
  
Procedure created.

Perform the modified stored procedure to reproduce the above error ORA-02069

[email protected]>exec pro_a;
BEGIN pro_a; END;

*
ERROR at line 1:
ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at "ZX.PRO_A", line 3
ORA-06512: at line 1

First, modify the Global_names parameter at the session level, execute the stored procedure again, and a new error has occurred: The database name on both sides is inconsistent.

[email protected]>alter session set global_names = true;

Session altered.

[email protected]>exec pro_a;
BEGIN pro_a; END;

*
ERROR at line 1:
ORA-02085: database link LINK_ORCL connects to ORCL
ORA-06512: at "ZX.PRO_A", line 3
ORA-06512: at line 1

[email protected]>!oerr ora 2085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.

That's the problem now, the actual production of the source and target database name is definitely inconsistent, so modifying this parameter does not solve the problem.

There are only a few other ways to get around this error, and here are two suggestions for development:

1. Deploy the stored procedure to the target side to avoid calling sequence in the remote insert

2, in the source-side storage process to introduce temporary tables, first insert data into the temporary table, and then from the temporary table inserted into the remote table.

A related document was found on MOS (ORA-02069 DURING REMOTE INSERT of a LOCAL SEQUENCE (document ID 1047673.6)) is consistent with our problem description.

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams098.htm#REFRN10065

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1907253

Oracle Stored procedure error ora-02069:global_names parameter must bes set to TRUE for this operation