Oracle DBLink Syntax Compatibility

Availability

This feature is available since MogDB 5.0.0.

Introduction

DBLink is known as database link, is a one-way connection between databases, usually used to create connections to external databases and perform DML operations on external data. This feature supports Oracle DBLink syntax, you can use @ symbol to access the tables in Oracle database directly in MogDB database through oracle_fdw plugin.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

This feature supports Oracle DBLink syntax to access tables in an Oracle database using the @ symbol.

Supports SELECT, INSERT, UPDATE, DELETE, and EXPLAIN operations via DBLink.

Constraints

Only SELECT, INSERT, UPDATE, DELETE, and EXPLAIN operations are supported.

Syntax Description

Connecting to an Oracle Database via DBLink
```
create server <dblink_name> foreign data wrapper oracle_fdw options(dbserver '<IP:PORT>/db_name'); 
```
- dblink_name: a customized DBLink name.
- IP:PORT: the IP address and port number of the server where the Oracle database resides.
- db_name: Oracle database name.
Create user mappings through DBLink.
```
create user mapping for <mogdb_user_name> server <dblink_name> options(user '<oracle_user_name>',password 'oracle_password');
```
- mogdb_user_name: the MogDB database user name.
- dblink_name: a customized DBLink name.
- oracle_user_name: the user name of the Oracle database.
- oracle_password: the user name and password for the Oracle database.

Querying tables via DBLink

SELECT * FROM <oracle_table_name>@<dblink_name>;

Inserting data via DBLink

INSERT INTO <oracle_table_name>@<dblink_name> VALUES (...);

Updating data via DBLink

UPDATE <oracle_table_name>@<dblink_name> SET... WHERE...;

Deleting data via DBLink

DELETE FROM <oracle_table_name>@<dblink_name> where ...;

Viewing the Execution Plan

explain SELECT * FROM <oracle_table_name>@<dblink_name>;

Note:

oracle_table_name: the table name of the Oracle database.

dblink_name: a customized DBLink name.

Example

Environmental preparation

MogDB

The MogDB database has been installed.
The oracle_fdw plugin is installed. See oracle_fdw for details.

Oracle

Logged into the database.

Create a table.

CREATE TABLE scott.EMPLOYEE (
ID INT PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
SALARY NUMBER(10,2)
);

Insert test data.

INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1001, 'Mike', 5000);
INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1002, 'JACK', 6000);

Steps

[omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r
gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=#

Create a user and give the user sysadmin permissions, using the username mymogdb50 as an example.

MogDB=# create user mymogdb50 identified by 'Enmo@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=# alter user mymogdb50 sysadmin;
ALTER ROLE

Exit the database.

MogDB=# \q
[omm5@localhost oracle_file]$

Create a user mapping key file for the user.

[omm5@localhost oracle_file]$ gs_guc generate -S 'xxxx@123' -D $GAUSSHOME/bin -o usermapping -U mymogdb50
The gs_guc run with the following arguments: [gs_guc -S ******** -D /data/mogdb500/app/bin -o usermapping -U mymogdb50 generate ].
gs_guc generate -S *** -U ***

Description: -S means customized key, e.g. xxxx@123.

[omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r -U mymogdb50 -W 'Enmo@123'
gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=>

Create a DBLink connection from the MogDB database to the Oracle database, using the IP address 127.2.15.23, port number 55446, and database name pdb_test as an example.
```
MogDB=> CREATE SERVER db_link_to_pdb_test_15_2 FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver '121.36.15.2:55446/pdb_test');
CREATE SERVER
```
Create a user mapping. Create a mapping for MogDB database user mymogdb50 and Oracle database user scott.
```
MogDB=> create user mapping for mymogdb50 server db_link_to_pdb_test_15_2 options(user 'scott',password 'xxx123');
CREATE USER MAPPING
```
Note: The username scott and the password xxx123 need to be replaced according to the actual environment.

Querying Oracle tables in a MogDB database via DBLink.

MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2;
  id  | name | salary
------+------+---------
 1001 | Mike | 5000.00
 1002 | JACK | 6000.00
(2 rows)

MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY > 400;
  id  | name | salary
------+------+---------
 1001 | Mike | 5000.00
 1002 | JACK | 6000.00
(2 rows)

Perform update, insertion, deletion, and query operations on Oracle database tables in a MogDB database through DBLink, using the scott.EMPLOYEE table as an example.

MogDB=> UPDATE scott.EMPLOYEE@db_link_to_pdb_test_15_2 SET SALARY = 5500 WHERE ID = 1001;
UPDATE 1

MogDB=> insert into scott.EMPLOYEE@db_link_to_pdb_test_15_2 values (1003, 'JANE', 7000);
INSERT 0 1

MogDB=> DELETE FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY =6000;
DELETE 1

MogDB=> select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
  id  | name | salary
------+------+---------
 1003 | JANE | 7000.00
 1001 | MIKE | 5500.00
(2 rows)

Performs the View Execution Plan operation on an Oracle database table in a MogDB database through DBLink.

MogDB=> explain select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
                                                    QUERY PLAN

-----------------------------------------------------------------------------------------------
-------------------
 Foreign Scan on "scott.employee@db_link_to_pdb_test_15_2" employee  (cost=10000.00..20000.00 r
ows=1000 width=78)
   Oracle query: SELECT /*66abc20a4a7895b75898e391381f9de8*/ r1."ID",r1."NAME",r1."SALARY" FROM
 scott.employee r1
(2 rows)

oracle_fdw