Skip to Main Content
  • Questions
  • Global Session Parameter must be true when bulk inserting over a database link using a local function

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 21, 2017 - 5:42 am UTC

Last updated: January 22, 2017 - 3:59 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

My actual use case is I need to transfer millions of records over a db link and need to make use of certain functions to transform
the data.

I am having issues when I try do a bulk insert across a database link and make use of a function inside
that bulk insert. For the purposes of this post I have broken this into it's simplest form

-- The link is called kyc_new
-- Table is created on kyc_new
   create table hasher(a varchar2(100)); 

-- Simple function is created on local database
create or replace function simple return varchar2 as 
begin
  return '1';
end simple;

-- Test if function runs when accessing a table over the db link
SQL> select simple from dual@kyc_new;

SIMPLE
--------------------------------------------------------------------------------
1


-- Bulk insert
insert into hasher@kyc_new select simple from dual
                                  *
ERROR at line 1:
ORA-02069: global_names parameter must be set to TRUE for this operation


Is there a way to do this without changing global session parameters
Aside for inserting into a temporary table and reinserting which I would prefer to avoid due to the fact that I need to transfer
millions of records and would prefer not needing to do this twice

and Connor said...

Sorry - not much you can do about it. Here's a MOS note about local sequences, but the local function is a similar issue

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. 


Dont discount the temporary table solution - if global_names was true, then you might the cost of calling that function across the link for each row is much slower anyway.

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library