This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Feb 26, 2013 2:00 AM by Luan Huynh RSS

Database Link ORACLE to POSTGRESQL.

Luan Huynh Newbie
Currently Being Moderated
Hi all.
Nice day !
I want to create connection from oracle to postgres.
Here are my configs:
1/ $oracle_home/hs/admin/initPSQL.ora
HS_FDS_CONNECT_INFO = PSQL
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so
set ODBCINI=/etc/odbc.ini

2/ listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)
     (SID_DESC=
          (SID_NAME=PSQL)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/)
          (PROGRAM=dg4odbc))
)

3/ tnsname.ora
PSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PSQL)
)
     (HS=OK)
)

5/ Driver POSTGRESQL ODBC:
Description=ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so


But when I create database link :

MY QUERY:
|
|
CREATE public DATABASE LINK l test
CONNECT TO "postgres" IDENTIFIED BY "123456"
USING 'PSQL';
SELECT id FROM tb_abc@luanmap_test ;


ERRORS:
|
|
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line 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

Please help me or give me some advices. Thanks.
Luan !
  • 1. Re: Database Link ORACLE to POSTGRESQL.
    asahide Expert
    Currently Being Moderated
    Hi,

    Did your "Create database link" Success ?
    CREATE public DATABASE LINK l test
    CONNECT TO "postgres" IDENTIFIED BY "123456"
    USING 'PSQL';
    SELECT id FROM tb_abc@luanmap_test ;
    your dblink name is named test , so you have to query follows..
    SELECT id FROM tb_abc@test;
    Regards,
  • 2. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    @asahideO : thanks. sry, name "test0" (not "luanmap_test"), i fixxed it. But I still get this error.

    @asahideO : thanks. sry, name "test0" (not "luanmap_test"), i fixxed it. But I still get this error.

    In .../hs/log
    I get:
    ||
    ||
    Oracle Corporation --- FRIDAY FEB 22 2013 12:23:44.404
    Heterogeneous Agent Release
    11.2.0.1.0
    HS Gateway: NULL connection context at exit
    ||
    ||

    Edited by: 970144 on 20:10 24-02-2013
  • 3. Re: Database Link ORACLE to POSTGRESQL.
    asahide Expert
    Currently Being Moderated
    Hi,

    Put results of dba_db_links and tnsping PSQL, pls.

    Regards,
  • 4. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    Look at HS_FDS_SHAREABLE_NAME - you have configured it pointing to the ODBC Driver "HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so" but all PostGres ODBC Drivers I know don't have all ODBC functions built in - hence it requires also an ODBC Driver Manager like for example the Driver Manager from www.unixODBC.org.

    So please check out if you already have an ODBC Driver Manager installed on your machine and if the word size of the Driver Manager matches the owrd size of the ODBC Driver as well as DG4ODBC. If you don't have an ODBC Driver manager, please get it from UnixODBC and apply it to your system. Once done, change the configuration and make sure HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager instead of the ODBC driver itself.


    - Klaus
  • 5. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    @kgronau : thanks.
    When I debug I get this error " Failed to load ODBC library symbol: /usr/lib/libodbcpsql.so(SQLAllocHandle) "
    So. I installed unixODBC and I try to fix all @@
  • 6. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    Root cause for the missing handle (SQLAllocHandle) is that the Postgres ODBC driver does not include generic ODBC functions and also requires a Driver Manager. So yes, please go ahead and apply the UnixODBC Driver Manager to your env.

    - Klaus
  • 7. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    @kgronau : I installed unixODBC using this command "yum install unixODBC".

    Your guide " so yes, please go ahead and apply the UnixODBC Driver Manager to your env "

    I understand "edit ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib in listener.ora" ??? Is is true

    PS: sry about my english skill ^_^.
  • 8. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    Installing unixODBC this way is commonly fine but please make sure it is a 2.3.x release.

    Regarding the LD_LIBRARY_PATH in the listener.ora file I commonly prefer to set it there as well, but in general just having all libs specified in the LD_LIBRARY_PATH for the user who starts the Oracle listener is also fine.

    When you specify it in the listener.ora file, please use:
    (ENV="LD_LIBRARY_PATH=<Oracle_Home>/lib:<UnixODBC>/lib:<ODBC-Driver>/lib")

    - Klaus
  • 9. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    I try to do but can not.

    hs/initpsqltest.ora
    ||
    HS_FDS_CONNECT_INFO = DNS_PSQL
    HS_FDS_TRACE_LEVEL = DEBUG
    HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
    set ODBCINI=/etc/odbc.ini
    ||

    Errors:
    ....
    [unixODBC][Driver Manager] Data Source name
    ....

    And in log file (hs/log)

    ....
    hgopoer, line 233: got native error 0 and sqlstate I; message follows...
    Exiting hgopoer, rc=0 at 2013/02/22-17:41:53
    hgocont, line 2752: calling SqlDriverConnect got sqlstate I
    Exiting hgocont, rc=28500 at 2013/02/22-17:41:53 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization
    Exiting hgolgon, rc=28500 at 2013/02/22-17:41:53 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont
    Entered hgoexit at 2013/02/22-17:41:53
    Exiting hgoexit, rc=0
    ......

    Edited by: 970144 on 00:19 25-02-2013
  • 10. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    Would be good to see also the error message from SQL*Plus ... so I can just guess and would suggest to add:
    HS_NLS_NCHAR = UCS2
    HS_LANGUAGE=american_america.we8iso8859p1
    to the gateway config file. Then please make sure to close SQL+plus and connect again, then test the DB link.

    - Klaus
  • 11. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    Thanks kgronau
    I know this error: "[unixODBC][Driver Manager]Data source name not found, and no default driver specified {IM002}"
    I try to solve it. Hope I can find soon.

    I find it: http://www.unixodbc.org/ODBCFlow.gif
  • 12. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    it commonly has to do with your odbc.ini file.

    So as the basic test you should test with isql, an ODBC test utility shipped with unixODBC. This isql tool requires the environment variable ODBCINI which must be set to the odbc.ini file. According to he gateway init file you're using this odbc.ini file: /etc/odbc.ini

    Does it contain a DSN pointing to your PostgreSQL database?
    The DSN will also include DRIVER parameter pointing to the ODBC Driver itself (DRIVER=/usr/lib/libodbcpsql.so) as well as Database, Servername and Port. There are a couple of additional parameters which you might have to set - so best would be to check the PostgreSQl ODBC docu for the driver version you're using.
  • 13. Re: Database Link ORACLE to POSTGRESQL.
    Luan Huynh Newbie
    Currently Being Moderated
    Sry:
    Your suggestion " (ENV="LD_LIBRARY_PATH=<Oracle_Home>/lib:<UnixODBC>/lib:<ODBC-Driver>/lib")"
    & I do "(ENV=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib)" Is is ok ?

    In /usr/lib I copy all lib (libodbc.so, libodbcpsql.so ...) into here.

    Here are 2 files: odbc.ini, odbcinst.ini (etc/odbc.ini ...)

    odbc.ini
    [DNS_PSQL]
    Description = Test by Luan
    Driver = PostgreSQL
    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

    odbcinst.ini
    [PostgreSQL]
    Description=ODBC for PostgreSQL
    Driver=/usr/lib/libodbcpsql.so
    FileUsage=1
    Setup=/usr/lib/libodbcpsqlS.so
  • 14. Re: Database Link ORACLE to POSTGRESQL.
    kgronau Guru
    Currently Being Moderated
    Yes, LD_LIBRARY_PATH setting is fine.
    When using a named Driver configuration Driver = PostgreSQL you also need to refer to the odbcinst.ini file - I commonly prefer to specify the driver and the full path in the odbc.ini file like:
    Driver=/usr/lib/libodbcpsql.so


    So once you exported ODBCINI environment variable:
    export ODBCINI=/etc/odbc.ini test the connection with isql (isql DNS_PSQL uid pwd)
1 2 3 Previous Next

Legend

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