通过数据链路向远程插入数据时使用本地用户函数的问题

显示全部楼层 · 2010-1-4 08:33:08

现有2台数据库在不同的机器上，A为本地数据库，B为远程数据库。
2个数据库的Global_names均为FALSE.
2库的版本都是一样是：9.2.0.7.0
B库上建表如下：
create table TEST
(
F1 VARCHAR2(100)
);
++++++++++++++++++++++++++++++++++++
A库上做如下操作：
1、创建数据链路到B库并测试：
SQL> select sysdate from dual@to_b;
SYSDATE
-----------
2008-6-11 1
SQL>
2、通过数据链路插入数据到测试表 :
SQL> insert into test@to_b(f1) values ('a');
1 row inserted
SQL>
3、创建一个函数并测试数据链路：
SQL> Create Or Replace Function f_convert(p_v1 In Varchar2) Return Varchar2 Is
2
v_result Varchar2(100);
3Begin
4
v_result := p_v1;
5
Return v_result;
6End;
7/
Function created
SQL> insert into test@to_b(f1) values (f_convert('a'));
insert into test@to_b(f1) values (f_convert('a'))
ORA-02069: 此操作的 global_names 参数必须设置为 TRUE
SQL>
问题：
客户机器global_names不能随便改动，因还有其他应用使用的数据链路，该如何处理？
函数也只能在本地创建，目标库只有数据插入的权限……

急啊……

千问 · 2010-1-4 08:33:08

这是在本地数据库操作时在远程机器上截取的日志信息：
*** 2008-06-11 15:14:12.881
*** SESSION ID

304.5423) 2008-06-11 15:14:12.880
APPNAME mod='ORACLE.EXE' mh=0 act='' ah=0
=====================
PARSING IN CURSOR #1 len=20 dep=0 uid=110 oct=3 lid=110 tim=1184734817266911 hv=
562528587 ad='573c6978'
SELECT * FROM "TEST"
END OF STMT
PARSE #1:c=976,e=613,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1184734817266432
=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=110 oct=2 lid=110 tim=1184734817284124 hv=
276591975 ad='583af168'
INSERTINTO "TEST" ("F1&quot

VALUES ('a')
END OF STMT
PARSE #1:c=0,e=326,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1184734817284121
EXEC #1:c=976,e=183,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=1184734817284409
*** 2008-06-11 15:14:24.602
=====================
PARSING IN CURSOR #1 len=20 dep=0 uid=110 oct=3 lid=110 tim=1184734828712961 hv=
562528587 ad='573c6978'
SELECT * FROM "TEST"
END OF STMT
PARSE #1:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1184734828712950
*** 2008-06-11 15:14:35.751
=====================
PARSING IN CURSOR #1 len=20 dep=0 uid=110 oct=3 lid=110 tim=1184734839600980 hv=
562528587 ad='573c6978'
SELECT * FROM "TEST"
END OF STMT
PARSE #1:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1184734839600962

千问 · 2010-1-4 08:33:08

这是在本地机器上截取的日志信息:
*** 2008-06-11 15:23:33.363
*** SESSION ID:(10.17) 2008-06-11 15:23:33.316
APPNAME mod='PL/SQL Developer' mh=1190136663 act='Command Window - New' ah=254318129
=====================
PARSING IN CURSOR #2 len=34 dep=0 uid=74 oct=42 lid=74 tim=21089329433 hv=3484365534 ad='6e6d9198'
alter session set sql_trace=true
END OF STMT
EXEC #2:c=0,e=39436,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=21089278638
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21089374370 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=465,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=21089374364
EXEC #1:c=0,e=2778,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21089388963
*** 2008-06-11 15:29:42.409
=====================
PARSING IN CURSOR #3 len=21 dep=0 uid=74 oct=3 lid=74 tim=21460920771 hv=3293499221 ad='6d85f7a0'
select 'x' from dual
END OF STMT
PARSE #3:c=15625,e=16342,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=21460920763
EXEC #3:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21460935961
FETCH #3:c=0,e=87,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=21460939208
=====================
PARSING IN CURSOR #1 len=116 dep=1 uid=0 oct=3 lid=0 tim=21460989227 hv=431456802 ad='6df77bb0'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #1:c=0,e=939,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=21460989220
EXEC #1:c=0,e=331,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=21461003542
FETCH #1:c=0,e=62,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=21461006524
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21461023578 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21461023572
EXEC #1:c=0,e=185,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21461039270
=====================
PARSING IN CURSOR #2 len=42 dep=0 uid=74 oct=2 lid=74 tim=21461045975 hv=2261733079 ad='6e6dc8b8'
insert into test@to_b(f1) values('a')
END OF STMT
PARSE #2:c=0,e=3337,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=21461045970
EXEC #2:c=0,e=1525,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21461062811
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21461093142 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21461093136
EXEC #1:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21461109365
*** 2008-06-11 15:29:54.081
=====================
PARSING IN CURSOR #3 len=21 dep=0 uid=74 oct=3 lid=74 tim=21472669633 hv=3293499221 ad='6d85f7a0'
select 'x' from dual
END OF STMT
PARSE #3:c=0,e=116,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21472669626
EXEC #3:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21472689820
FETCH #3:c=0,e=86,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=21472693468
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21472743686 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21472743680
EXEC #1:c=0,e=177,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21472760789
=====================
PARSE ERROR #2:len=54 dep=0 uid=74 oct=2 lid=74 tim=21472767985 err=2069
insert into test@to_b(f1) values(f_convert('a'))
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21472804401 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21472804396
EXEC #1:c=0,e=294,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21472821781
*** 2008-06-11 15:30:05.177
=====================
PARSING IN CURSOR #3 len=21 dep=0 uid=74 oct=3 lid=74 tim=21483814944 hv=3293499221 ad='6d85f7a0'
select 'x' from dual
END OF STMT
PARSE #3:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21483814936
EXEC #3:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21483835916
FETCH #3:c=0,e=86,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=21483839774
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21483892337 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=494,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21483892331
EXEC #1:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21483910226
=====================
PARSE ERROR #2:len=54 dep=0 uid=74 oct=2 lid=74 tim=21483917355 err=2069
insert into test@to_b(f1) values(f_convert('a'))
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21483957411 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21483957406
EXEC #1:c=0,e=187,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21483975153
*** 2008-06-11 15:30:20.376
=====================
PARSING IN CURSOR #3 len=21 dep=0 uid=74 oct=3 lid=74 tim=21499106403 hv=3293499221 ad='6d85f7a0'
select 'x' from dual
END OF STMT
PARSE #3:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21499106396
EXEC #3:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21499127984
FETCH #3:c=0,e=87,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=21499131894
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
=====================
PARSING IN CURSOR #1 len=61 dep=0 uid=74 oct=47 lid=74 tim=21499180867 hv=2707513792 ad='6d901034'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21499180861
EXEC #1:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=21499199209
=====================
PARSING IN CURSOR #2 len=35 dep=0 uid=74 oct=42 lid=74 tim=21499203869 hv=3542426350 ad='6e6cedf4'
alter session set sql_trace=false
END OF STMT
PARSE #2:c=0,e=219,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=21499203862
EXEC #2:c=0,e=100,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=21499222106

千问 · 2010-1-4 08:33:08

关注一下

千问 · 2010-1-4 08:33:08

千问 · 2010-1-4 08:33:08

大家帮顶啊，论坛牛人快来帮忙啊 ……

千问 · 2010-1-4 08:33:08

关注一下，没遇到过这种问题

千问 · 2010-1-4 08:33:08

这个问题，总结出来一句话就是：本地通过链路网远程写数据，不能调用本地函数。可以调用远程函数，insert into test@to_b(f1) values (f_convert('a')@to_b);
并且在函数后面加上链路名。

千问 · 2010-1-4 08:33:08

出错信息不是说了吗，alter session set global_names = true

千问 · 2010-1-4 08:33:08

我一般这样做，建一张远库的临时表，先把数据插到临时表，在把临时表数据插入到远库的表