DBLink read feature in Oracle mode
OceanBase DBLink enables cross-data-source access. This way, business code can access remote databases as if they were local databases. DBLink also supports distributed transactions across data sources, which reduces the complexity of business code.
In Oracle mode, the read feature of DBLink is available. You can use this feature to access an OceanBase database or a native Oracle database in remote mode.
After you create a DBLink to a remote database, you can use DBLink to access remote objects (such as tables, views, and synonyms) and then drop the DBLink and view the DBLink.
DBLink write feature in Oracle mode
In Oracle mode, DBLink supports data write operations. You can use a DBLink (DBLink) to perform data operations on remote database objects. These remote objects can be tables, views, or synonyms. Although the write feature of DBLink is similar to the read feature in terms of operation methods, the two features support different types of SQL statements. Specifically, the read feature is mainly concerned with query operations, whereas the write feature supports the INSERT, DELETE, UPDATE, and MERGE INTO data manipulation language (DML) statements.
The write feature of DBLink is supported in Oracle mode but not in MySQL mode. You can use DBLink to write data to a remote OceanBase (Oracle mode) or native Oracle database.
When you create a DBLink, if you want to access a local database from a remote database (that is, to use the reverse link feature), you must provide the IP address, port number, username, tenant name, and password of the local database. The reverse link feature allows a remote database to access objects in the local database, such as tables, views, or synonyms. This feature is particularly useful in scenarios that require bidirectional data interaction.
If you do not want to use the reverse link feature, you can choose not to provide these additional parameters when you create a DBLink. However, the reverse link feature is supported only for database access between two Oracle modes, but not for database access from an Oracle mode to a native Oracle database. Therefore, if you provide these additional parameters when you attempt to create a DBLink to a native Oracle database, the creation will fail due to a syntax error.
PL DBLink
You can remotely call stored procedures in OceanBase Database through a DBLink (DBLink). This way, you can conveniently call remote stored procedures in OceanBase Database to support distributed data processing and operations.
Currently, you can remotely call only standalone procedures and stored procedures in PL packages, but not user-defined functions (UDFs) or functions in PL packages.