背景描述:一个正在开发的项目用到OCILIB操作oracle数据库,需要定时插入大量位置数据,同时需要向其他不同的远程数据库实例进行同步插入数据,而远程数据库表结构中用到oracle自定义对象类型SDO_GEOMETRY
,在批量插入时不能对这种对象类型进行操作,而通过本地触发器将本地数据插入到远程数据库进也不能对这种对象类型进行操作。想到的解决方法有两个:1)在远程数据库建一个临时的不包含SDO_GEOMETRY
对象的临时表,将数据插入该表后,用远程数据库的触发器进行转移,缺点是数据量大时,触发器会不稳定;2)是在远程数据库建立一存储过程,程序中调用存储过程进行数据的批量插入或者在本地数据库建立一触发器调用过程数据库的存储过程来进行数据转发(数据库版本低于10.2.0.2.0会有ORA-04052错误),前提是要在两个数据库实例之间建立DBLink连接。
2.1建立与远程数据库的DBLink
(1)建立连接实例
修改 tnsnames.ora 文件(Windows XP 系统上默认在
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora),添加如下代码:
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.201)(PORT =
1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
(2)获取dblink
的权限
创建 dblink 之前,必须有创建 dblink 的权限。以 sys 身份登录:
sqlplus sys/change_on_install as sysdba;
查看 dblink 的权限:
select * from user_sys_privs t where t.privilege like
upper('%link%');
CREATE
DATABASE LINK:所创建的
dblink 只有创建者能使用,其它用户不能使用
CREATE PUBLIC DATABASE LINK:所创建的
dblink 所有用户都可以使用
DROP PUBLIC DATABASE LINK:删除
dblink
以 sys 身份把 CREATE PUBLIC DATABASE LINK 和 DROP PUBLIC DATABASE LINK 权限授予 clgl 用户:
grant CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE
LINK to clgl;
以 clgl 身份登录,创建 dblink:
create public database link to_TEST connect to jcjd
identified by "jcjd" using ‘TEST’;
其中:to_TEST是所创建的
dblink 的名字,TEST是远程数据库的实例名,jcjd/jcjd
是登录到远程数据库的帐号/密码。
(3)测试连接是否成功
select * from dual@to_TEST
如果返回结果如下则表示连接成功了。
DUMMY
-----
X
(4)查询已经建立的远程连接名
select owner,object_name from dba_objects where
object_type='DATABASE LINK';
OWNER OBJECT_NAME
-------------------
CLGLTEST
(5)在远程数据库上建立存储过程
create or replace procedure proc_insertGPS(
carNO in
varchar2, --车牌号码
realDate
in
date, --时间
longitude in
number, --经度
latitude in
number, --纬度
velocity in
number, --经度
orientation
in
number --纬度
)
as
begin
insert into
T_GPS(ID,SBLX,SBBH,TBMC,GXSJ,SHAPE,SD,FX) values(jcjd.seq_t_gps_jlzy.nextval,'2',carno,'11.png',realdate,mdsys.sdo_geometry(2001,
8307, mdsys.sdo_point_type(longitude,latitude, 0),null,null)
,velocity,orientation);
end;
(6)本地数据库建立触发器调用远程数据库的存储过程
create or replace trigger
trigger_TEST
after insert on LSGJB
for each row
declare
begin
proc_insertGPS@to_TEST(:new.sbbh,:new.sj,:new.x,:new.y,:new.sd,:new.fx);
end;
(7)程序中调用远程数据库的存储过程见另一节
OCILIB批量插入数据或调用存储过程批量插入