You can use the dblink_ora_connect() function to establish a connection.

SELECT dblink_ora_connect('acctg', 'localhost', 'xe', 'hr', 'pwd', 1521);

This example connects to a service named xe running on port 1521 on the localhost with a username of hr and a password of pwd. You can use the connection name acctg to reference this connection when calling other dblink_ora functions.

The following statement uses the dblink_ora_copy() function over a connection named polardb_conn. It copies the empid and deptno columns from a table named ora_acctg on an Oracle server to a table named as_acctg located in the public schema of a PolarDB cluster compatible with Oracle. The TRUNCATE option is enforced, and a feedback count of 3 is specified.

polardb=# SELECT dblink_ora_copy('polardb_conn','select empid, deptno FROM ora_acctg', 'public', 'as_acctg', true, 3);
INFO:  Row: 0
INFO:  Row: 3
INFO:  Row: 6
INFO:  Row: 9
INFO:  Row: 12

 dblink_ora_copy
-----------------
 12

(1 row)

The following statement uses the dblink_ora_record() function and the acctg connection to retrieve information from the Oracle server:

SELECT * FROM dblink_ora_record( 'acctg', 'SELECT first_name from employees') AS t1(id VARCHAR);

This statement retrieves a list that includes all of the entries in the first_name column of the employees table.