1 2 3 Previous Next 34 Replies Latest reply: Feb 26, 2013 4:00 AM by Luan Huynh Go to original post RSS
      • 15. Re: Database Link ORACLE to POSTGRESQL.
        Luan Huynh
        Thanks !!!
        I'm sorry kgronau, i don't know "isql".
        When I run command , it's get error.
        [oracle@vnlamp ~]$ isql DNS_PSQL
        [ISQL]ERROR: Could not SQLConnect


        I changed [Driver              = /usr/lib/libodbcpsql.so]
        But it still get error:
        "ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
        [unixODBC][Driver Manager]Data source name not found, and no default driver specified {IM002}
        ORA-02063: preceding 2 lines from LUANMAP_TEST
        28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
        *Cause: The cause is explained in the forwarded message.
        *Action: See the non-Oracle system's documentation of the forwarded
        message.
        Error at Line: 9 Column: 33"

        Edited by: Luan Huynh on 01:07 25-02-2013
        • 16. Re: Database Link ORACLE to POSTGRESQL.
          Kgronau-Oracle
          to check what's going wrong with isql please use:
          isql -v DNS_PSQL

          The error you get in SQL*Plus might be related to the missing ODBC DSN or odbc.ini privs. This can be best figured out using strace

          1. get the PID of your gateway listener (ps -ef|grep listener)
          2. strace -fae -o dg4odbc.log -p <PID of the gateway listener>
          3. open SQL*Plus and perform again a select using your database link
          4. once you got the error, kill strace and check out the generated dg4odbc.log file - best would be to check for odbc.ini

          - Klaus
          • 17. Re: Database Link ORACLE to POSTGRESQL.
            Luan Huynh
            Thanks for your following with me so far.
            Until now. I don't understand something. I think I have at least lose 1 week to do this task.
            Because in files: listener.ora, tnsname.ora, initpsqltest.ora, odbc.ini (of course: host, un, pass.. is ok).
            But I have problem about DRIVER ?
            I installed unixODBC (2.3.1) (http://www.linuxfromscratch.org/blfs/view/svn/general/unixodbc.html)
            And psqlodbc (yum install odbc-postgresql)

            @_@
            • 18. Re: Database Link ORACLE to POSTGRESQL.
              Kgronau-Oracle
              Well, it is not a driver, but a config issue. DG4ODBC relies on a working ODBC connection. isql allows you to test the ODBC connection natively - when it fails you have an issue within your ODBC configuration and commonly the ODBC vendor would be able to assist you. I can do it also but I would need some diagnostic info.
              Here again strace would be helpful. It will log all calls made by isql and where it tries to locate the files. Syntax is again strace -fae -o <output file>. For isql we could for example use:
              strace -fae -o isql.log isql -v DNS_PSQL
              to trace the isql process.
              Once you got the error, post the isql.log and the error message and we will try to see if we can assist you to solve the issue.



              Once you have a working ODBC connection we could have a look at the DG4ODBC connection.


              If you have a support contract feel free to file a service request and we could assist you this way as well (it will allow us for example to check out the issue online in an Oracle Web Conference).
              If you want me to have a look, please provide the strace output.
              • 19. Re: Database Link ORACLE to POSTGRESQL.
                Luan Huynh
                Hi kgronau ! Nice day to you !
                I try to follow your guide !

                Here are 2 files:
                ** dg4odbc.log
                4578 waitpid(-1, <unfinished ...>

                // just 1 line

                *** isql.log
                -------------------------------------------------------------------------------------------------------------------------
                -------------------------------------------------------------------------------------------------------------------------
                4888 execve("/usr/local/bin/isql", ["isql", "-v", "DNS_PSQL"], [* 29 vars */]) = 0
                4888 brk(0) = 0x9006000
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f81000
                4888 access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/tls/i686/sse2/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/tls/i686/sse2", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/tls/i686/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/tls/i686", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/tls/sse2/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/tls/sse2", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/tls/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/tls", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/i686/sse2/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/i686/sse2", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/i686/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/i686", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/sse2/libodbc.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/usr/local/lib/sse2", 0xbfaa488c) = -1 ENOENT (No such file or directory)
                4888 open("/usr/local/lib/libodbc.so.2", O_RDONLY) = 3
                4888 read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0P>\0\0004\0\0\0"..., 512) = 512
                4888 fstat64(3, {st_mode=S_IFREG|0755, st_size=1695577, ...}) = 0
                4888 mmap2(NULL, 440424, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x745000
                4888 mmap2(0x7ac000, 16384, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x66) = 0x7ac000
                4888 mmap2(0x7b0000, 2152, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x7b0000
                4888 close(3) = 0
                4888 open("/usr/local/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/tls/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/tls/i686/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/tls/i686/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/tls/i686", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/tls/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/tls/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/tls/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/tls", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/i686/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/i686/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/i686", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/u01/app/oracle/product/11.2.0/grid/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/u01/app/oracle/product/11.2.0/grid/lib", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/tls/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/tls/i686/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/tls/i686/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/tls/i686", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/tls/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/tls/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/tls/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/tls", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/i686/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/i686/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/i686/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/i686", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
                4888 open("/lib/sse2/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 stat64("/lib/sse2", 0xbfaa4870) = -1 ENOENT (No such file or directory)
                4888 open("/lib/libdl.so.2", O_RDONLY) = 3
                4888 read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0p*b\0004\0\0\0"..., 512) = 512
                4888 fstat64(3, {st_mode=S_IFREG|0755, st_size=20668, ...}) = 0
                4888 mmap2(0x622000, 16504, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x622000
                4888 mmap2(0x625000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x2) = 0x625000
                4888 close(3) = 0
                4888 open("/usr/local/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/i686/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/libpthread.so.0", O_RDONLY) = 3
                4888 read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0p\210e\0004\0\0\0"..., 512) = 512
                4888 fstat64(3, {st_mode=S_IFREG|0755, st_size=137944, ...}) = 0
                4888 mmap2(0x654000, 102884, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x654000
                4888 mprotect(0x669000, 4096, PROT_NONE) = 0
                4888 mmap2(0x66a000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x15) = 0x66a000
                4888 mmap2(0x66c000, 4580, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x66c000
                4888 close(3) = 0
                4888 open("/usr/local/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/i686/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/libc.so.6", O_RDONLY) = 3
                4888 read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\340\337q\0004\0\0\0"..., 512) = 512
                4888 fstat64(3, {st_mode=S_IFREG|0755, st_size=1697920, ...}) = 0
                4888 mmap2(0x708000, 1418692, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x110000
                4888 mprotect(0x264000, 4096, PROT_NONE) = 0
                4888 mmap2(0x265000, 12288, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x154) = 0x265000
                4888 mmap2(0x268000, 9668, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x268000
                4888 close(3) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7f80000
                4888 set_thread_area({entry_number:-1 -> 6, base_addr:0xb7f80ac0, limit:1048575, seg_32bit:1, contents:0, read_exec_only:0, limit_in_pages:1, seg_not_present:0, useable:1}) = 0
                4888 mprotect(0x265000, 8192, PROT_READ) = 0
                4888 mprotect(0x66a000, 4096, PROT_READ) = 0
                4888 mprotect(0x625000, 4096, PROT_READ) = 0
                4888 mprotect(0x61e000, 4096, PROT_READ) = 0
                4888 set_tid_address(0xb7f80b08) = 4888
                4888 set_robust_list(0xb7f80b10, 0xc) = 0
                4888 futex(0xbfaa5144, FUTEX_WAKE_PRIVATE, 1) = 0
                4888 rt_sigaction(SIGRTMIN, {0x658400, [], SA_SIGINFO}, NULL, 8) = 0
                4888 rt_sigaction(SIGRT_1, {0x658300, [], SA_RESTART|SA_SIGINFO}, NULL, 8) = 0
                4888 rt_sigprocmask(SIG_UNBLOCK, [RTMIN RT_1], NULL, 8) = 0
                4888 getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=RLIM_INFINITY}) = 0
                4888 uname({sys="Linux", node="vnlamp", ...}) = 0
                4888 brk(0) = 0x9006000
                4888 brk(0x9027000) = 0x9027000
                4888 open("/usr/lib/locale/locale-archive", O_RDONLY|O_LARGEFILE) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=56450624, ...}) = 0
                4888 mmap2(NULL, 2097152, PROT_READ, MAP_PRIVATE, 3, 0) = 0xb7d80000
                4888 close(3) = 0
                4888 time(NULL) = 1361591787
                4888 open("/usr/local/etc/odbcinst.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=431, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "[PostgreSQL]\n#Description=ODBC f"..., 4096) = 431
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbcinst.ini", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 open("/usr/local/etc/odbcinst.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=431, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "[PostgreSQL]\n#Description=ODBC f"..., 4096) = 431
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbcinst.ini", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 getuid32() = 500
                4888 socket(PF_FILE, SOCK_STREAM, 0) = 3
                4888 fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
                4888 connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
                4888 close(3) = 0
                4888 socket(PF_FILE, SOCK_STREAM, 0) = 3
                4888 fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
                4888 connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 ENOENT (No such file or directory)
                4888 close(3) = 0
                4888 open("/etc/nsswitch.conf", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=1696, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "#\n# /etc/nsswitch.conf\n#\n# An ex"..., 4096) = 1696
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/usr/local/lib/libnss_files.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/i686/libnss_files.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
                4888 open("/lib/libnss_files.so.2", O_RDONLY) = 3
                4888 read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\340\30\0\0004\0\0\0"..., 512) = 512
                4888 fstat64(3, {st_mode=S_IFREG|0755, st_size=50848, ...}) = 0
                4888 mmap2(NULL, 45712, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x933000
                4888 mmap2(0x93d000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x9) = 0x93d000
                4888 close(3) = 0
                4888 mprotect(0x93d000, 4096, PROT_READ) = 0
                4888 open("/etc/passwd", O_RDONLY) = 3
                4888 fcntl64(3, F_GETFD) = 0
                4888 fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=1736, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "root:x:0:0:root:/root:/bin/bash\n"..., 4096) = 1736
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 llseek(3, 0, [0], SEEKSET) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbc.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 open("/usr/local/etc/odbc.ini", O_RDONLY) = 3
                4888 close(3) = 0
                4888 open("/usr/local/etc/odbc.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=417, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "\n[DNS_PSQL]\n Description "..., 4096) = 417
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 getuid32() = 500
                4888 open("/etc/passwd", O_RDONLY) = 3
                4888 fcntl64(3, F_GETFD) = 0
                4888 fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=1736, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "root:x:0:0:root:/root:/bin/bash\n"..., 4096) = 1736
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 llseek(3, 0, [0], SEEKSET) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 open("/home/oracle/.odbc.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 open("/usr/local/etc/odbc.ini", O_RDONLY) = 3
                4888 close(3) = 0
                4888 open("/usr/local/etc/odbc.ini", O_RDONLY) = 3
                4888 fstat64(3, {st_mode=S_IFREG|0644, st_size=417, ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 read(3, "\n[DNS_PSQL]\n Description "..., 4096) = 417
                4888 read(3, "", 4096) = 0
                4888 close(3) = 0
                4888 munmap(0xb7d7f000, 4096) = 0
                4888 time(NULL) = 1361591787
                4888 time(NULL) = 1361591787
                4888 fstat64(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 2), ...}) = 0
                4888 mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7d7f000
                4888 write(1, "[IM002][unixODBC][Driver Manager"..., 93) = 93
                4888  write(2, "[ISQL]ERROR: Could not SQLConnec"..., 34) = 34
                4888 exit_group(1) = ?

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

                I can not attach this file. So I upload into here.
                • 20. Re: Database Link ORACLE to POSTGRESQL.
                  Luan Huynh
                  Ah !
                  I using:
                  Centos 5.5
                  Postgresql: 9.0
                  psql-odbc: 8.

                  Some info:

                  [oracle@vnlamp ~]$ odbcinst -j
                  unixODBC 2.3.1
                  DRIVERS............: /usr/local/etc/odbcinst.ini
                  SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
                  FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
                  USER DATA SOURCES..: /usr/local/etc/odbc.ini
                  SQLULEN Size.......: 4
                  SQLLEN Size........: 4
                  SQLSETPOSIROW Size.: 2

                  [oracle@vnlamp ~]$ odbcinst -q -d
                  [PostgreSQL]

                  // [PostgreSQL] is contained in /etc/odbcinst.ini & /usr/local/etc/odbc.ini

                  Edited by: Luan Huynh on 20:48 25-02-2013
                  • 21. Re: Database Link ORACLE to POSTGRESQL.
                    Kgronau-Oracle
                    Looks like the odbc.ini file unixODBC is trying to use is not located in /etc - it looks for the file in /usr/local/etc

                    so let's first start with isql and make sure your environment is correctly sourced:
                    export ODBCINI=/usr/local/etc/odbc.ini
                    isql -v DNS_PSQL
                    Are you now able to connect?
                    • 22. Re: Database Link ORACLE to POSTGRESQL.
                      Luan Huynh
                      About path file odbc.ini, it's ok.
                      [root@vnlamp etc]# find / -name "odbc.ini" -print
                      /etc/odbc.ini
                      /usr/local/etc/odbc.ini
                      [root@vnlamp etc]#

                      I get errors when running:

                      [oracle@vnlamp ~]$ isql -v DNS_PSQL
                      [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
                      [ISQL]ERROR: Could not SQLConnect

                      .... i'm thinking ..

                      Edited by: Luan Huynh on 22:04 25-02-2013

                      Edited by: Luan Huynh on 22:05 25-02-2013
                      • 23. Re: Database Link ORACLE to POSTGRESQL.
                        Kgronau-Oracle
                        provide me the output of:
                        env
                        isql -v DNS_PSQL
                        • 24. Re: Database Link ORACLE to POSTGRESQL.
                          Luan Huynh
                          Ok kgronau

                          It's in here (server test):
                          ------------------------------------------------------------------------------------------------------------------------------------------------
                          ------------------------------------------------------------------------------------------------------------------------------------------------
                          [oracle@vnlamp ~]$ env
                          TEPDIR=/tmp
                          HOSTNAME=vnlamp
                          TERM=xterm
                          SHELL=/bin/bash
                          HISTSIZE=1000
                          SSH_CLIENT=10.1.101.159 55914 22
                          SSH_TTY=/dev/pts/2
                          USER=oracle
                          LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/grid/lib:/lib:/usr/lib
                          LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
                          ORACLE_SID=orcl
                          ORACLE_BASE=/u01/app/oracle
                          MAIL=/var/spool/mail/oracle
                          PATH=/u01/app/oracle/product/11.2.0/grid/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
                          INPUTRC=/etc/inputrc
                          PWD=/home/oracle
                          LANG=en_US.UTF-8
                          ODBCSYSINI=/etc
                          SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                          SHLVL=1
                          HOME=/home/oracle
                          ODBCINI=/usr/local/etc/odbc.ini.
                          GRID_HOME=/u01/app/oracle/product/11.2.0/grid
                          TMP=/tmp
                          LOGNAME=oracle
                          CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/JRE:/u01/app/oracle/product/11.2.0/grid/jlib:/u01/app/oracle/product/11.2.0/grid/rdbms/jlib
                          SSH_CONNECTION=10.1.101.159 55914 10.1.101.161 22
                          LESSOPEN=|/usr/bin/lesspipe.sh %s
                          ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
                          G_BROKEN_FILENAMES=1
                          _=/bin/env
                          [oracle@vnlamp ~]$ isql -v DNS_PSQL
                          [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
                          [ISQL]ERROR: Could not SQLConnect
                          [oracle@vnlamp ~]$
                          ------------------------------------------------------------------------------------------------------------------------------------------------
                          ------------------------------------------------------------------------------------------------------------------------------------------------
                          • 25. Re: Database Link ORACLE to POSTGRESQL.
                            Kgronau-Oracle
                            the environment variable ODBCINI has a typo . it is configured to use the file ODBCINI=/usr/local/etc/odbc.ini. (with a dot at the end).

                            Please correct it and by the way, just to clarify, the content of this odbc.ini file is:

                            [DNS_PSQL]
                            Description = Test by Luan
                            Driver = /usr/lib/libodbcpsql.so
                            DNS = DNS_PSQL
                            Trace = No
                            TraceFile = /tmp/odbc.log
                            Database = postgres
                            Servername = 10.1.101.161
                            UserName = postgres
                            Password = 123456
                            Port = 5432
                            Servertype = postgres
                            ReadOnly = No


                            If isql still fails to connect, please provide again:
                            env
                            more $ODBCINI
                            isql -v DNS_PSQL
                            • 26. Re: Database Link ORACLE to POSTGRESQL.
                              Luan Huynh
                              Thank kgronau very much.
                              I modified:
                              + initpsqltest.ora --> set ODBCINI from /etc/odbc.ini --> /usr/local/etc/odbc.ini
                              + export ODBCINI=/usr/local/etc/odbc.ini
                              + reboot
                              --> It's work !

                              Results:
                              [oracle@vnlamp ~]$ isql -v DNS_PSQL
                              ---------------------------------------
                              | Connected! |
                              | |
                              | sql-statement |
                              | help [tablename] |
                              | quit |
                              | |
                              ---------------------------------------
                              SQL>


                              I'm very happy .
                              Thanks , again !!!

                              And now, I will try to get data from postgresql. But I dont know what it happen :)

                              Edited by: Luan Huynh on 22:52 25-02-2013
                              • 27. Re: Database Link ORACLE to POSTGRESQL.
                                Kgronau-Oracle
                                When its working, please close this thread and feel free to spend some credit points.

                                Thanks,
                                Klaus
                                • 28. Re: Database Link ORACLE to POSTGRESQL.
                                  Luan Huynh
                                  Dear kgronau

                                  "isql" is ok !

                                  But when running this command (below) (using SQL_DEV on WINDOW to test, connect orcl / linux)

                                  CREATE public DATABASE LINK test
                                  CONNECT TO "postgres" IDENTIFIED BY "123456"
                                  USING 'PSQL_TNS';

                                  SELECT * FROM "pst.test"@test ;


                                  It raise error :
                                  "ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                                  ORA-02063: preceding line from TEST
                                  28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
                                  *Cause: The cause is explained in the forwarded message.
                                  *Action: See the non-Oracle system's documentation of the forwarded message.
                                  Error at Line: 11 Column: 17
                                  "

                                  Of course user's info is right.
                                  • 29. Re: Database Link ORACLE to POSTGRESQL.
                                    Kgronau-Oracle
                                    In your gateway init file, please set HS_FDS_TRACE_LEVEL=255
                                    Then open a new SQL*Plus window and execute this select: select user from dual@test;
                                    Provide me the output and have a look at hs/log directory - it will contain a gateway trace file. Please post the content as well as your current gateway init file.