Re: dblink_ora - a first shot on Oracle ...

This seems like a natural addition to our existing dblink in /contrib.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> 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
>

[ application/x-gzip is not supported, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073