dblink_ora - a first shot on Oracle ...

Hi there ...

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/tiger(at)dbname');
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();

[hs(at)sabrina 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