Re: dblink_ora - a first shot on Oracle ... - Mailing list pgsql-hackers

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

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

Hans-J�rgen Sch�nig 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@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
> 

[ 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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073