This topic describes how to call dblink_ora functions.
The following command uses the dblink_ora_connect() function to establish a connection.
SELECT dblink_ora_connect('acctg', 'localhost', 'xe', 'hr', 'pwd', 1521);
In this example, the hr username and the pwd password are used to connect to the xe service that runs on port 1521 on the localhost. You can use the connection name acctg to reference this connection when you call other dblink_ora functions.
The following command uses the dblink_ora_copy() function for the polardb_conn connection. It replicates the empid and deptno columns from the ora_acctg table on an Oracle server to the as_acctg table that is located in the public schema of a PolarDB for PostgreSQL(Compatible with Oracle) instance. The TRUNCATE option is enforced and the feedback count 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 SELECT 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 command retrieves a list that contains all the entries in the first_name column of the employees table.