Calling dblink_ora Functions v12

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 and a password of pwd. You can use the connection name acctg to refer to this connection when calling other dblink_ora functions.

The following command uses the dblink_ora_copy() function over a connection named edb_conn to copy 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 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


(1 row)

The following SELECT statement uses 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.