I am experiencing some errors with the following and would greatly appreciate the advice of some experts here.
My use-case is to insert some records into a table via a database link. The records to be inserted will be queried from an identical table in the local data dictionary. Everything works, but occasionally I will get a unique constraint violation if I try to insert a duplicate record so I wrote a simple function to check for the scenario. My issue is that I can run my procedure using the db link and I can run my function using the db link, but I can't use them both together without getting errors.
My test case just uses the standard emp table:
create or replace procedure test_insert(p_instance varchar2)
IS
l_sql varchar2(4000);
begin
l_sql := 'insert into EMP@'||p_instance||' (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) (Select EMPNO, ENAME, JOB, MGR, SAL, DEPTNO from EMP)';
execute immediate l_sql;
END;
BEGIN
test_insert('myLink');
END;
This works fine and the insert occurs without any issues.
If I run the same procedure a second time I get:
00001. 00000 - "unique constraint (%s.%s) violated" which is what I expect since EMPNO has a unique constraint. So far so good.
Now I create a function to test whether the record exists:
create or replace function record_exists(p_empno IN NUMBER, p_instance IN varchar2) return number
IS
l_sql varchar2(4000);
l_count number;
BEGIN
l_sql := 'select count(*) from EMP@'||p_instance||' where empno = '||p_empno;
execute immediate l_sql into l_count;
IF
l_count > 0
THEN return 1;
ELSE
return 0;
END IF;
END;
I test this as follows:
select record_exists(8020, 'myLink') from dual;
RECORD_EXISTS(8020,'myLink')
-------------------------------------------
1
That works ok, so now I will add that function to my procedure:
create or replace procedure test_insert(p_instance varchar2)
IS
l_sql varchar2(4000);
begin
l_sql := 'insert into EMP@'||p_instance||' (EMPNO, ENAME, JOB, MGR, SAL, DEPTNO) (Select EMPNO, ENAME, JOB, MGR, SAL, DEPTNO from EMP WHERE record_exists( EMPNO, '''||p_instance||''') = 0)';
execute immediate l_sql;
END;
I test this as follows:
BEGIN
test_insert('myLink');
END;
Result is:
Error report:
ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at "FUSION.TEST_INSERT", line 6
ORA-06512: at line 2
02069. 00000 - "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
*Action: Issue alter session set global_names = true if possible
I don't know why I am getting this. The function works, the procedure works, but when I combine them I get an error. If I set the global names parameter to true and then rerun this I get:
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.
Any advice is much appreciated. I don't understand why I can run the procedure and the function each separately over the db link but they don't work together.
thank you,
john