This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Feb 26, 2013 2:00 AM by Luan Huynh Go to original post RSS
  • 15. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    provide me the output of:
    env
    isql -v DNS_PSQL
  • 24. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points