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@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@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