1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Having trouble using dg4odbc to connect Oracle to Postgres

Discussion in 'General' started by hairytorus, Jul 18, 2011.

  1. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I am trying to reference remote PostgreSQL tables from an Oracle instance. I am trying to use heterogeneous services to do this. I have created an odbc link to the remote database that is confirmed working (I used isql).

    Here is my tnsnames.ora:

    # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    LISTENER_ORCL =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))


    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )

    REMOTEDB=
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=TCP)
    (HOST=dbs1)
    (PORT=5432)
    )
    (CONNECT_DATA=
    (SID=REMOTEDB)
    )
    (HS=OK)
    )

    Here is my listener.ora:

    # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST=
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 5432))
    )
    )
    )

    ADR_BASE_LISTENER = /opt/oracle

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=REMOTEDB)
    (ORACLE_HOME=/opt/oracle/product/11gR1/db)
    (PROGRAM=dg4odbc)
    (ENVS=LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/11gR1/db/lib)
    )
    )

    TRACE_LEVEL_LISTENER = 0
    LOGGING_LISTENER = off

    Here is my initREMOTEDB.ora:

    # This is a sample agent init file that contains the HS parameters that are
    # needed for the Database Gateway for ODBC

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = REMOTEDB
    HS_FDS_TRACE_LEVEL = 0
    #HS_FDS_TRACE_FILE_NAME = /tmp/oracle_trace_host_143
    HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbcw.so

    HS_FDS_SUPPORT_STATISTICS = FALSE
    #
    # ODBC specific environment variables
    #
    set ODBCINI=/etc/unixODBC/odbc.ini

    #
    # Environment variables required for the non-Oracle system
    #
    #set <envvar>=<value>

    Here are the results from tnsping REMOTEDB:

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-JUL-2011 16:17:17

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=dbs1) (PORT=5432)) (CONNECT_DATA= (SID=REMOTEDB)) (HS=OK))
    OK (0 msec)

    However, when I execute the following SQL,

    create public database link odbc_bridge_to_remote_db connect to "definitely_correct_username" identified by "definitely_correct_password" using 'REMOTEDB';
    select * from "correctly_spelled_and_formatted_name_of_existing_table"@odbc_bridge_to_remote_db;

    I receive the following error:

    [Err] ORA-12154: TNS:could not resolve the connect identifier specified

    Does anyone know why? I have tried what feels like every combination of parameters and so I thought this is probably as good a broken place to start as any.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you enclosing the username and password in double quotes?


    create public database link odbc_bridge_to_remote_db connect to definitely_correct_username identified by definitely_correct_password using 'REMOTEDB';
     
  3. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    If I remove the double quotes here is the error I get:

    [Err] ORA-00933: SQL command not properly ended
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have special characters in the login or the password, I suspect. Is there a properly configured tnsnames.ora file on the Oracle database server?
     
  5. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    The username and password are both alphanumeric. I have retyped them manually with no success. The tnsnames.ora is included in the original message. For the sake of brevity I will kindly refer you there rather than re-post it.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which doesn't answer my question of where that tnsnames.ora resides. How many oracle installations are on that server? How many tnsnames.ora files are installed on that server?
     
  7. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    There is one Oracle installation.

    I did this:
    cd /
    sudo find . -name tnsnames.ora

    Which got me this:
    ./opt/oracle/product/11gR1/db/network/admin/samples/tnsnames.ora
    ./opt/oracle/product/11gR1/db/network/admin/tnsnames.ora

    Do you think the other tnsnames.ora in the samples folder is interfering somehow? I am quite certain that file was just included by the Oracle installation process and has never been modified by myself or my colleagues.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the complete output from

    tnsping remotedb

    I want to see where Oracle says your sqlnet.ora file resides.
     
  9. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 19-JUL-2011 12:34:11

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=dbs1) (PORT=5432)) (CONNECT_DATA= (SID=REMOTEDB)) (HS=OK))
    OK (10 msec)
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your sqlnet.ora file, please.
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Separate the statements then report which one generates the error.
     
    hairytorus likes this.
  12. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    # sqlnet.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    ADR_BASE = /opt/oracle
     
  13. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Which statements do you mean? I am only performing a single select statement.
     
  14. hairytorus

    hairytorus Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Ok problem solved. We were working with Oracle support in parallel. The following changes were necessary:

    In initREMOTEDB.ora, replace:

    HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbcw.so

    with:

    HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

    That apparently says HD_FDS_SHAREABLE_NAME should be the Unix ODBC driver manager and not the Postgres-specific ODBC driver.

    Also add:

    HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

    How did I miss that?

    Finally, the following line from listener.ora should be removed:

    (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 5432))
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have both the create database link statement and the select statement posted as if they are executed from a script. You also posted only one error message. I see the solution involved configuration changes; thank you for posting the solution so others can learn and properly configure HS.
     
  16. malak.esa

    malak.esa Guest

    can i connect to postgresql from oracle sqlplus ?