X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 9B5AA30DCD1 for ; Fri, 13 Jun 2003 19:51:23 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 95316-02 for ; Fri, 13 Jun 2003 16:51:13 -0300 (ADT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id E954830D196 for ; Fri, 13 Jun 2003 16:51:11 -0300 (ADT) Received: (qmail-ldap/ctrl 69656 invoked from network); 13 Jun 2003 19:51:07 -0000 Received: from unknown (HELO waste.silverserver.co.at) ([194.152.178.7]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with SMTP for ; 13 Jun 2003 19:51:07 -0000 Received: from cybertec.at (vie-213-129-225-028.async.sil.at [213.129.225.28]) by waste.silverserver.co.at (8.12.9/8.12.8) with ESMTP id h5DJp0LH014151; Fri, 13 Jun 2003 21:51:01 +0200 Message-ID: <3EEA2CCF.3040509@cybertec.at> Date: Fri, 13 Jun 2003 21:58:07 +0200 From: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-hackers@postgresql.org, mail@joeconway.com, EG Subject: dblink_ora - a first shot on Oracle ... Content-Type: multipart/mixed; boundary="------------040100000600070305070200" X-Archive-Number: 200306/361 X-Sequence-Number: 39668 This is a multi-part message in MIME format. --------------040100000600070305070200 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 --------------040100000600070305070200 Content-Type: application/x-gzip; name="dblink_ora.tar.gz" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="dblink_ora.tar.gz" H4sIACsp6j4AA+1c/3faRrbPr9JfMS+vrbGDMRAnqeO6XQzCVoOBBZxuz74e jhADaCMkKgk77Jf//X3unZGQbOxs9zXd17Oes40lzdw79965X2eGnU58L/gw DiPn6NnnatXqcfXNq1f4W62+eV38q9szfHhTP64fH798+axaq1XrtWfi1Wej KNfWceJEQjxbxI+P+1T/77RNt+t/5XyQM8+Xv/oc1Vq1+vr4+MH1f31cy9a/ +hK6UKsfv6o+E9VfnZId7T98/Tv2+fCPnd6g8fW4P7Da9p/OjtZxdOSHruMf +d4EimE2m2dz1xWHvbo4/MHxfbPZ77c7jYvh2aH9Rekehv0jL3D99VSKB7qB lv6Lf/aB/evc6IrZaWnEnYcgxaGvAQUNQnezY40ve1dW2u36QRIv8LBKFpF0 pmb/IqWWeYsj92gVxsk8ksB0+KbysvKSP2pCTFPxvZpX4tB4a6hHV6i/C9P4 otRs7osvShovPaYS2ReHs5UHWaXD8Rjqx3ALqdnE6HjhRHJKg3KTZgBon3n9 c/Y/WwduBRL51ef4hP0f117VM/uvvz5m+3/z+sn+f4t2eChi6Us3EVMncSrC ESsZxV6cyCARbhgE6PLCQCwhJzGRIlzJwGwNen3Rvu42R3avK7YqVErkx2T/ 1GwOrMbIEr2BGFj9TqNpPTwYI0bXg+5QDK1Rr423Zm/QMhtDsfewrYKsJIIP yeluznj2RKfRvbhuXFhir7kn7Kur61HjvGOJ4WhgN0enpgmeiQ0vmD/IbhKK ZCFZJhMnlg9yrEF+EeMFmIx/evv8fE+9WE//IEvbIaV/lqM8yG/M0K+1kL8v ruVH+XGdSHDthsulE0wf5Asj3V+knVuAjCcvSI4/O0+fsZHEhlbHao7EgWgP eld5L7SHRUySo8Sby+gP00ngLOVeWQDE2NMwTmUqV0kQlumJ+ukBCZJGhT7h 7O0LCKjUsvqjbo+VoCxa3caVpZ87vabQq2BqtPcdwi5SAPAw6bpHKqJix1dD 5HKl6eEeTQJ1axI03N65dWF3we2d1dffcwOv+y3SHCAmP82ozvjfQ1HbAf/o 8BzaQa/TOW803+1AkXX9PxfAi18mgBcFAXwGjj7TSjV7V1f2aAcC3bEd+pBL /dyJ7FP7l1rOYeuC5def4/H8v1Z/Wcv2f+ov668p/6+/fsr/f5N2dCBsLnsp iXoerqPnVIhGTrQRB0fmf6e1+XNd/D43TUCk+RVA7uRXBETl+Xg6GS+Ql/hy nIhciHNR7ooDP5x7wb75N9O4N3a5mU4Wp6ZhCmFgJi/wEsIpDG8mjBLtB4yH 182mNRyK/4KnInAao3p67XZZ1PC/anV/X5jG34AFeAyJCUvWYNAblMXztuP5 qLhBOCPfbkT8T/Acfgrj/2Hq2ZlOnv6x+VPevmLaywrq4fmbThCEicZ96yUL 8WWMqVO4lAIjksk6ClKB/IMFjxx3FkZL5H0/ryWWCFITehytAlQ5kUvKgJU8 s9WIk2VSWA8G16uRQW1XpDh+uYkTXhMIwSjFSeTLoLSFEt+I2j4xe4fRbqip XEXhjTeVUxIvWGM8EKaoim9FieejLL5OAsREFGMWZdMQumUTlUW1LLrXnc6+ Fu5uwaYy4rkr4srZoGzdhGvI5EYisLrhOkgkbbo4It4EifNRyCgKI4hlO4wQ 6XVVutKDDkOgG5loLrSKzGXCog/XyWpNMP56GQiK0bFWnEBoPQnROeaekmb0 q8ClyPgpTALynieLDKF/D+MYIzKsLCKgVdLp9kZ204J4oLs5BYnXrivjeLb2 n/PYTNt4qZW2ResAGiaKgKFStUguQxQgS8ddeAErWv9inJYTY7vb7o3f10rF bAETtZxkvbybROQhG4OLIevhHd0EyxfWaIyCgYbjEQPHI+tPo3G/BGPHkqBK Ec5splbsTFRPDZJqsF5OJNZ2JqLwNhYyHTDZ3LEYcGAaOXgWMJPHRr1V99O7 gm1ooLfiyynMOMXBNMFeMpzfiOouM9FqS1NRSVeQdqprBnhW1RhEO3pZL+Um UWvVVxUZQd/CVcMIBKwg1WFBe3tcD4NNjP6B3A7xv5D+ioSTLLx4OwhuEa8o 8mJvAqWH+qd4ktBE1gql0PaQ+X3yRBjnS7Iewpyrwcm6P60hGmCHkqQO9hE9 Ud70kzpCybI4QOG69pMxvZDqPxrQ4IcOyCGRQBwx98MJkuMbJ/IckkwciluM DYO9RPkNQN+GUbRhMGcCWyZhJmEolmt3QUIwCNlZPiamft/QpCxXns+UsJkR qcrwVcsRDzQsqguZjPDaL7W8CAjbehmbju/XuJT3grJoDlGgB3OMZZjS8967 5/v7HG5U26qYFllupsfUzIngUKdKaFnGX1C4x9d9C7Rj6XM1xM7V37GgKoLN ENzjBdlujsV/n+wMEl5LElVY2VhGN5BcjKC4FRSZq7aqT8psh6R2y4f4IV6a YcBcGwc0n5t8hJxG65UvWzJ2DSNZr6Z4gFAoeChnahiGC8ixGyQR+1MNMSLN H/pwWYZxEOPvVgyNJLGDK5k4ZD3U7SSQ3xIfyM6UpaqU4FOWqty5p/04e/J1 DB/qBSw0B8lSuEozMwpn8Jjr+FTcbwyrusnRkXII4iuNplCvGNGWaTsQN6c7 oG8cfy2L49egYx6AoHgRRtDAG//0zvh80DY086s5AjbF6z83e52O1f3pVLHG QZ75g/9g2SHJWruIyPCjkXSygJV30oSXWE+Wq1RECBqrkHP3zEPlpUYyi1Wg I0ezkO4H0kZSPLLemReRJCCb+xEh54BUIjxoj+3huG0PhqNmo9MpZSnZPW+6 1WnufMj9KbpYGuGtjkCJt2SXejftZVxIVBQilcvyv+RFDYWlI5O9mJMqKvBI ghSipiF8NpDm/FgkfRZwnKxnM4X6CslNtNE2YxihP3XV86kmUS0K6MkEpAeo oBshch6yILNNaVfmZZg2bYzggwSaSRMWb49KKjUsNtJl1AwIJRDLksnMpoY7 jsIV1h2UERlLeCAPBguS9VSK0q3yF9uWTdBTEMGQpxyFJU3v4beMe8z+QVEx xuf9VDyxTJI0gul0m+itZkqwxeN8HCuCzlC15ZRgLgMZEScJ+RxB3inyVpni 30nB2hyCvpzqrDlGGsapdd6zNXnNRjARWu/M+ZUCtyxmjh/LjPz7CjhZe/40 ywty1pkyxNZCABjyQW44iocB58valOJwltw6OZCMAhtlqAUnuylpYpFDJgkS V/VXbQT+7I/xkmxW3rQsDmtcC2mqFUasuFGCL2CXSU4B+Wbg8tOLF8g72TiN GPoRJLNS5okgPM5aMYzlZTxGFmF9QQV2Drz+6i49wEIpq0GxAdRkCBE+KWyk 6Hhgpgh6sIonhpEFjjsIChFmN6Y8aC7+qMITcl84MS8p523KUuQNeRU2WJJl 0Sr71oBtcogA3yejLITFbNrsI2UgipkCc/icp+w+uToVJeVDTnnrgZiZJEtn h8V6xy5/dmcb4uxMlHR8O9PlNMOVuBisIU/RVQeHIATlAxXPSGiX0lmxeIXB dkbfVGJhGCqFUT7IyCpUEhkSpTCapi7EMG7SckkhLmVVKE2vnZiRL3FVaFSm oVnzELviHEq/gFPVt4XqNkPKGq1dDZfM23ANPOoZgtERfl+ssFAhmT0y9Nj7 qwxn3Jemc2xJiHAq90AKoszoxQsvMyKaDwSxueAvYb/x/+z9RLaxL74VKriL 79KHtyLrZhhFFH3J6MrIInz36CLDDbTlZsBlooHMl7zdDT5sLU/5zDNxTm6L 17cdhUudxcalTOXKWlYMqUsNbW9Zrku6W1ZeOI2uZBU63e0iZ0ujQlnn16pm TaOK9KmI0pnBvdQ8H/XhJhEzfDmjgind1uJVzE3Y6nWtdEKeyKDK5Ol04V9t 9/b/F7/+HJ+4/1N7VX2V2/9/Q/v/L6tP9/9+k1bY/2/qvX9Puc9s9/+bOJl6 YWXxbeETOZO736ZAQN/MDK/M4UxTbZh3LgEvnDOkNwvoqGH7dbacR8Uvarss jI7ilVfsWXqx60yXXlD8vE48Pz6iRA7OL97VN1+7uz778QYIUS3dIQk+yFFz mwUh6nonKye2IOqcIYWY0m4A7aB+ZHdP0dAp7PoSrB6kAokBOzLvH6/w+cCO bXveyTWL9W2wq769u2l9p8SlcHcQ+KcPgOQL3QJM4KoQeme27Y6sTtYBKMyM VdoXoJ2AkgsNW+3/H3ZpFAJkP3nUtOVAADnMGu5B3OEaAbAfelTGZMgVDiD/ nQefnP8fWI3WlfUZ5mD///rh898q3fkv3v9//ebNk///LdrRgckb3EoLPjii 9J62LFBmVyvV2j46qT81Cp2VkZvjrH2GtHLHaYCGam5g6Il0xQWKtMWtF8AJ flURQ3cRysCb05BFkqzeHh3d3t5WtnfKKk5CfZdOEB9+v5bRHDlxCiS+WcR/ cDViDPw2nStcbSJvvkiQUe+LOtSqTP/Wad8sF2su1AZ+CxWfH6744OciCtcr QoEiTwzsi8vRUAysoTV4b7VO6Tv915cR4kqsbxSuYxS+LmYsi2U49Wb4y5tL HgXFCZ3iFEt+6vSSmCUdumua1sn2fJ1gI1brCOwDKZ0H06HBTEqFM/3giNvI SxJIwgErfDhE6DDLQkYSTM4jBw5qWs5OW0GDo+oiZxLeSKZXSQiptufqIxvQ SXhWTgS8zmqhv1JdjHrklndxbsNtf0zbTBIW44ESlKjAisheSeVkd0W3J6z3 Vnckhpck0dGlJRrXo8vegC4etmy68nd+PeoNhuLcEh2b7wGOeqLR/VH0G4PR j6LdGxCqlj2wmqMyUKZPw77VtBsd+tS0W5iCnoG02esOrT9e4wN6Ratx1biw hjyqc92yuxeErdMbjkR/0GvbI3Q1BvYQHaJ3PRK9NtN4PbTUoz0Uw1579ENj AMK7LQEAJqfXvL6iOWmDvcwsCrv9GHuXjfcWeMS4Ruu9PbRaeirWp95waJ/b HRv84itq+UtNeCbKHGYio4CaJdG2aV/iR+ppdhr21ZBlCLoHDUgCElALooVQ FoDF4owg8yt7BGpGvTJPYl/1Ozbet5BE0pU1aF7itaGoZAMBGZBfl3Yd2kQW L5jdvO40BqJ/PQBTVkUwzkyCEPl7rFVLXFoD67rbsgbCZnn2IJaueN4Y4v25 OG8MbVoXzPAY35cYDv3qnXfsC16IIbhgeappxFXD7o6sbqNL24LD636/N4Di qFtuwG91L6mL1nHIqnPVa7EcGVdF+RJUpOYPZDu0pZd8Z55tm2mOyLBh9Gu4 OzLx9YoMd6orVmS5N/JR11gxbcojAtjORm8y0ucpnatv/dReLBLevs/tL08B a5pXsN0bdh5vi4Q1ZrTLunBu1G1oPww/CPDwfYjsMQxunQ1wKjfP20vsLIjq RXpgG1Mul87n+F6yESb5J9TlQJzSTyy78BKxZqwi2nxsUMA5DYk1Ah42+qJ1 LibkwYhAE8g2+sBUOyq6ur0GYa5oHjVfvBCcZ80cNztdla5yZ6GYe6BVC9QR SbSpmOdO7LksTHa5JCbe4ZtIuMrUZfIJh+SrCnO64iB9yrXBYgJEyxAeuomw RNtsTXO+jtZYYXaULJuYMGlf6qU9dGI/UXKbSLAfI5UluVXEZXhL24llksBt GH2IzQDOFvTxlrzyuku+k5IJNyZx3krfh3LwlhiFDA4bfHcGerCM1U0htctF p4ZS+lAxybvTsSScLEiHNgxP90lTQAjLbHsCUlDlvEqnZz9qBya7BKNXMlZ3 pYAE/n57XUrcLjxM5kLHeS/1NvBDhxbKJLqIYbKAQoBHDHQXH2RlKvO//4IK 8QkUJrrRuQeKg21iat5KRQ+fZW0BD+uVl5XqKpKvKuCvMv9rxez7ki4jqNjF 0iK6abMeWAt8JFs7htS3l354DqViMJhe087p45QLBHA2ka6DgWqJ1/5UgbPK zRw66ghNOgCATqflbrrz+ZdwwtvO5Ab4Q0DHLXEiVwWxq6yCB6S/yhSHHG93 Lk6ssJtY/gXZQKTorJhdSIEgaJESGSsatPdCzhCtA8rl3prmCjLljebpRHwj 0l+CmSSZcKXTGdZmaNafkYHFzgQ1k8PneL786Qt1UsJYeJ4cjsIvE9LfIGTv /67e+9dLdv0GwRS9d2apRneHUOmp46a3Qjx0lWo7Qh1I1dMS11QXuoX4O12u zmZ6kc4yRES+pN43J1+blIgiacBb7RW9Io62BL/Wj/H6fQ8BmF7rJ29emoh4 CMHdrPe803hnce/X9IrEYPCOX4/pddjsjUYKljC/oxyIXo/5lTZYEZ+B6pjJ QELCE9Wq9Pp940rNe0KYEP8VUQrTlQ2aGbSO11LtmK9b5WW2vSV1WDP57hM+ Pnb3aRdo7Th/avmdku53uU9/v3O37BM/LHlxf8EfuxEP9PkGau7rxpbYqrml 8BG6dtCQ/vjhznzA+KSLT7qofkbyz+ji59SNk6JunBR146SoG8dF3Tgp6sZJ UTdOirpxUtSNk6JunBR146SgGydF3Tgp6sbJJ3TjP8TZqF/wMDsPOZudF/Ie 4LYYMs1IghvCtqxVTqpvYhN5U6Q+VCvV6texGW9isf3wOgZQe82XK1a+E8R3 klbTPBSjO6k8H5DrNB7d/Sike4lLyoVpFCWTqF6SzUrG6FYXwjmnnApnOlU1 XKTqlbroo3qQh81wuUR2V7J1rfKB74TMRL3fzMq+SF/LQpKIUpt6kbBPwumG CxCmxwvo3pLa6+GL05yv0f9BwEplqnThxnETZO9EuY2cUdmqs837VJrNtQ2l lCiy0logpBQ0SG/b0tYTMkFOdYHLChZO4PK9JmficTnHdYGxc1vNNErFfbX9 3/Wu9lN7ak/tqT21p/bUntpTe6j9L3ViG+cAUAAA --------------040100000600070305070200--