DBLink read feature in Oracle-compatible mode
OceanBase DBLink (hereinafter referred to as 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-compatible mode, the read feature of DBLink is available. You can use this feature to access a remote OceanBase database (Oracle-compatible mode) or a native Oracle database.
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-compatible mode
In Oracle-compatible mode, DBLink supports data write operations. You can use a database link (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-compatible mode but not in MySQL-compatible mode. You can use DBLink to write data to a remote OceanBase database (Oracle-compatible 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 need the reverse link feature, you can choose not to provide this additional information when creating a DBLink. Note that the reverse link feature is supported only for database access between OceanBase Oracle-compatible modes, and is not supported for access from OceanBase Oracle-compatible mode to a native Oracle database. Therefore, if you provide this additional information when creating 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. 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.