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
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.