Calling dblink_ora functions v17

Using the dblink_ora_connect function

The following command establishes a connection using the dblink_ora_connect() function:

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

The example connects to:

• A service named xe
• Running on port 1521 on the localhost
• With a user name of hr
• With a password of pwd

You can use the connection name acctg to refer to this connection when calling other dblink_ora functions.

Using the dblink_ora_copy function

The following command uses the dblink_ora_copy() function over a connection named edb_conn. It copies the empid and deptno columns from a table on an Oracle server named ora_acctg to a table located in the public schema on an instance of EDB Postgres Advanced Server named as_acctg. The TRUNCATE option is enforced, and a feedback count of 3 is specified:

edb=# SELECT dblink_ora_copy('edb_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)

Using the dblink_ora_record function

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);

The command retrieves a list that includes all of the entries in the first_name column of the employees table.