From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Hans-Jürgen Schönig <postgres@cybertec.at>
Cc: pgsql-hackers@postgresql.org, mail@joeconway.com, EG <EG@cybertec.at>
Subject: Re: dblink_ora - a first shot on Oracle ...
Date: Mon, 23 Jun 2003 13:34:27 -0400 (EDT)
Message-ID: <200306231734.h5NHYRP18946@candle.pha.pa.us> (raw)
In-Reply-To: <3EEA2CCF.3040509@cybertec.at>
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, Pennsylvania 19073
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pgman@candle.pha.pa.us, postgres@cybertec.at, EG@cybertec.at
Subject: Re: dblink_ora - a first shot on Oracle ...
In-Reply-To: <200306231734.h5NHYRP18946@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox