I have spent some time working on an Oracle version of dblink. It works quite nicely for me and I hope it does for others.
It already supports some basic features such as persistent connection and fetching data. This is not a perfect piece of software and there is lot of room for enhancing this stuff.
If there is somebody out there who is interesting in this kind of stuff I would be glad.
Maybe I will have some time in the next few days so that I can provide an interface for flat files and some other database such as Berkley DB as well. Maybe there will also be a version for MySQL but this one will be used for MIGRATION purposes only. In other words: I won't touch MySQL - just for migration and to get rid of it.
Personal thanks to Joe Conway, most of the code has been stolen from him.
Here is what you can do with the Oracle version:
SELECT dblink_oraconnect('scott/[EMAIL PROTECTED]'); SELECT * FROM dblink_ora('SELECT ename, sal FROM emp') AS (ename text, sal text); SELECT 'BEGIN', dblink_oraexec('BEGIN'); SELECT 'UPDATE emp SET sal = sal - 1', dblink_oraexec('UPDATE emp SET sal = sal - 1'); SELECT 'ROLLBACK', dblink_oraexec('ROLLBACK'); SELECT * FROM dblink_ora('SELECT ename, sal FROM emp') AS (ename text, sal text); SELECT 'BEGIN', dblink_oraexec('BEGIN'); SELECT 'UPDATE emp SET sal = sal + 1', dblink_oraexec('UPDATE emp SET sal = sal + 1'); SELECT * FROM dblink_ora('SELECT ename, sal FROM emp') AS (ename text, sal text); SELECT 'UPDATE emp SET sal = sal - 1', dblink_oraexec('UPDATE emp SET sal = sal - 1'); SELECT 'COMMIT', dblink_oraexec('COMMIT'); SELECT dblink_oradisconnect();
[EMAIL PROTECTED] dblink_ora]$ psql test < func.sql DROP FUNCTION CREATE FUNCTION DROP FUNCTION CREATE FUNCTION DROP FUNCTION CREATE FUNCTION DROP FUNCTION CREATE FUNCTION DROP FUNCTION CREATE FUNCTION dblink_oraconnect ------------------- OK (1 row)
NOTICE: SQL statement successful NOTICE: Found 2 columns ename | sal --------+------ SMITH | 798 ALLEN | 1598 WARD | 1248 JONES | 2973 MARTIN | 1248 BLAKE | 2848 CLARK | 2448 SCOTT | 2998 KING | 4998 TURNER | 1498 ADAMS | 1098 JAMES | 948 FORD | 2998 MILLER | 1298 (14 rows)
NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal - 1 | 14
(1 row)NOTICE: Affected: 0 ?column? | dblink_oraexec ----------+---------------- ROLLBACK | 0 (1 row)
NOTICE: SQL statement successful NOTICE: Found 2 columns ename | sal --------+------ SMITH | 798 ALLEN | 1598 WARD | 1248 JONES | 2973 MARTIN | 1248 BLAKE | 2848 CLARK | 2448 SCOTT | 2998 KING | 4998 TURNER | 1498 ADAMS | 1098 JAMES | 948 FORD | 2998 MILLER | 1298 (14 rows)
NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal + 1 | 14
(1 row)NOTICE: SQL statement successful NOTICE: Found 2 columns ename | sal --------+------ SMITH | 799 ALLEN | 1599 WARD | 1249 JONES | 2974 MARTIN | 1249 BLAKE | 2849 CLARK | 2449 SCOTT | 2999 KING | 4999 TURNER | 1499 ADAMS | 1099 JAMES | 949 FORD | 2999 MILLER | 1299 (14 rows)
NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal - 1 | 14
(1 row)NOTICE: Affected: 0 ?column? | dblink_oraexec ----------+---------------- COMMIT | 0 (1 row)
dblink_oradisconnect ---------------------- OK (1 row)
Regards,
Hans
-- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at
dblink_ora.tar.gz
Description: GNU Zip compressed data---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
