背景介绍
oracle.date和oracle.varchar2是LightDB中新增的类型,对应于Oracle数据库的date和varchar2类型。oracle_fdw是第三方插件,所以不支持oracle.date和oracle.varchar2类型。从LightDB24.1版本开始,oracle_fdw插件开始支持oracle.date和oracle.varchar2类型。
使用示例
- 在Oracle数据库中创建原始表,并插入数据
CREATE TABLE bxy_t1(id int PRIMARY key, name varchar2(10), curr_time date);
INSERT INTO BXY_T1 values(1, 'hong', to_date('1980-12-17','yyyy-mm-dd HH24:mi:ss'));
INSERT INTO BXY_T1 values(2, null,null);
INSERT INTO BXY_T1 values(3, 'haha', to_date('2024-01-08 12:11:11', 'yyyy-mm-dd HH24:mi:ss'));
INSERT INTO BXY_T1 values(8, '我们', to_date('2023-01-08','yyyy-mm-dd HH24:mi:ss'));
INSERT INTO BXY_T1 values(5, 'hong', to_date('1980-12-17','yyyy-mm-dd HH24:mi:ss'));
- 在Lightdb中创建oracle兼容数据库;
create database oracle_test LIGHTDB_SYNTAX_COMPATIBLE_TYPE oracle;
- 创建oracle_fdw连接,oracle数据库的IP和端口脱敏处理
create extension oracle_fdw ;
create server dblnk_g6_ctp foreign data wrapper oracle_fdw options (dbserver '//xxx.xxx.xxx.xxx:xxxx/test');
create user mapping for lightdb server dblnk_g6_ctp options (USER 'test',PASSWORD 'test' );
CREATE foreign TABLE BXY_T1(ID INT OPTIONS(key 'true'), NAME VARCHAR2(10), CURR_TIME DATE) server dblnk_g6_ctp options (schema 'TEST', table 'BXY_T1');
- 对bxy_t1这张表进行增删改查操作
lightdb@oracle_test=# select * from bxy_t1;
id | name | curr_time
----+------+---------------------
1 | hong | 1980-12-17 00:00:00
2 | |
3 | haha | 2024-01-08 12:11:11
8 | 我们 | 2023-01-08 00:00:00
5 | hong | 1980-12-17 00:00:00
(5 rows)
lightdb@oracle_test=#
lightdb@oracle_test=# update bxy_t1 set name='ming', curr_time=sysdate where id=2;
UPDATE 1
lightdb@oracle_test=#
lightdb@oracle_test=# select * from bxy_t1;
id | name | curr_time
----+------+---------------------
1 | hong | 1980-12-17 00:00:00
2 | ming | 2024-03-14 03:08:56
3 | haha | 2024-01-08 12:11:11
8 | 我们 | 2023-01-08 00:00:00
5 | hong | 1980-12-17 00:00:00
(5 rows)
lightdb@oracle_test=# delete from bxy_t1 where id = 3;
DELETE 1
lightdb@oracle_test=# select * from bxy_t1;
id | name | curr_time
----+------+---------------------
1 | hong | 1980-12-17 00:00:00
2 | ming | 2024-03-14 03:08:56
8 | 我们 | 2023-01-08 00:00:00
5 | hong | 1980-12-17 00:00:00
(4 rows)
lightdb@oracle_test=#