13 Replies Latest reply: May 28, 2014 8:41 PM by 2671659 RSS

    [Oracle to PostgreSQL]connction string lacks some options

    2671659

      Hi guys,

      I want to connect from oracle 11g to PostgreSQL 9.3 . l get this error message:

       

      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

      [unixODBC]connction string lacks some options {08001,NativeErr = 202}

      ORA-02063: preceding 2 lines from k

       

      but I can connected to PostgreSQL using isql.

       

      Oracle CharacterSet :AL32UTF8

      PostgreSQL server_encoding:UTF8

      PostgreSQL client_encoding:UTF8

       

      Here are my configuration files:

      odbc.ini:

      [PostgreSQL]

      Driver = /usr/local/pgsqlodbc/lib/psqlodbcw.so

      Description = Test2PG

      Servername = 192.168.0.1

      PORT = 5432

      Protocol = 9.3

      UserName = postgres

      Password = postgres

      Database = testdb

      TRACE =Yes

      TaceFile = /tmp/sql.log

       

      tnsnames.ora:

      PostgreSQL =

      (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))

         (CONNECT_DATA =(SID = PostgreSQL)

         )

        (HS =OK)


      listener.ora:

      SID_LIST_LISTENER=

        (SID_LIST=

          (SID_DESC=

            (SID_NAME=PostgreSQL)

            (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

            (EVNS=LD_LIBRARY_PATH=/usr/local/unixodbc/lib:/usr/local/pgsqlodbc/lib:/u01/app/oracle/product/11.2.0/dbhome_1/bin)

            (PROGRAM=dg4odbc)

           )

         )

       

      initiPostgreSQL.ora:

      HS_FDS_CONNECT_INFO = PostgreSQL

      HS_FDS_TRACE_LEVEL = 255

      HS_FDS_SHAREABLE_NAME = /usr/local/unixodbc/lib/libodbc.so

      HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

      set ODBCINI=/etc/odbc.ini


      Trace:

      Oracle Corporation --- THURSDAY  MAY 15 2014 15:59:19.875

      Heterogeneous Agent Release

      11.2.0.3.0

      Oracle Corporation --- THURSDAY  MAY 15 2014 15:59:19.874

          Version 11.2.0.3.0

      Entered hgogprd

      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"

      Entered hgosdip

      setting HS_OPEN_CURSORS to default of 50

      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"

      setting HS_FDS_RECOVERY_PWD to default value

      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG

      setting HS_IDLE_TIMEOUT to default of 0

      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"

      setting HS_NLS_NCHAR to default of "AL32UTF8"

      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"

      setting HS_FDS_DATE_MAPPING to default of "DATE"

      setting HS_RPC_FETCH_REBLOCKING to default of "ON"

      setting HS_FDS_FETCH_ROWS to default of "100"

      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"

      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"

      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"

      setting HS_FDS_MAP_NCHAR to default of "TRUE"

      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"

      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"

      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"

      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"

      setting HS_FDS_QUERY_DRIVER to default of "TRUE"

      setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"

      setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"

      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"

      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"

      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"

      Default value of 32 assumed for HS_FDS_SQLLEN_INTERPRETATION

      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"

      setting HS_FDS_DELAYED_OPEN to default of "TRUE"

      setting HS_FDS_WORKAROUNDS to default of "0"

      Exiting hgosdip, rc=0

      ORACLE_SID is "PostgreSQL"

      Product-Info:

        Port Rls/Upd:3/0 PrdStat:0

        Agent:Oracle Database Gateway for ODBC

        Facility:hsa

        Class:ODBC, ClassVsn:11.2.0.3.0_0011, Instance:PostgreSQL

      Exiting hgogprd, rc=0

      hostmstr:          0: HOA After hoagprd

      hostmstr:          0: HOA Before hoainit

      Entered hgoinit

      HOCXU_COMP_CSET=1

      HOCXU_DRV_CSET=31

      HOCXU_DRV_NCHAR=873

      HOCXU_DB_CSET=873

      HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1

      LANG=en_US.UTF-8

      HOCXU_SEM_VER=112000

      Entered hgolofn at 2014/05/15-15:59:19

      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/unixodbc/lib/libodbc.so"

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLAllocHandle

      symbol_peflctx=0x522520

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLBindCol

      symbol_peflctx=0x5226c0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLBindParameter

      symbol_peflctx=0x523110

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLCancel

      symbol_peflctx=0x524930

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLDescribeParam

      symbol_peflctx=0x52e120

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLDisconnect

      symbol_peflctx=0x52e670

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLEndTran

      symbol_peflctx=0x531b50

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLExecute

      symbol_peflctx=0x5336b0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLFetch

      symbol_peflctx=0x534070

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLFreeHandle

      symbol_peflctx=0x5361e0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLFreeStmt

      symbol_peflctx=0x536210

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetData

      symbol_peflctx=0x537fd0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetEnvAttr

      symbol_peflctx=0x53bc40

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetFunctions

      symbol_peflctx=0x53c040

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLMoreResults

      symbol_peflctx=0x53e0d0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLNumResultCols

      symbol_peflctx=0x53f030

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLParamData

      symbol_peflctx=0x53f3f0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLPutData

      symbol_peflctx=0x541a40

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLRowCount

      symbol_peflctx=0x541f30

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLSetEnvAttr

      symbol_peflctx=0x5447f0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLSetDescRec

      symbol_peflctx=0x544570

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLColAttribute

      symbol_peflctx=0x5254a0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLColumns

      symbol_peflctx=0x527280

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLConnect

      symbol_peflctx=0x52b600

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLDescribeCol

      symbol_peflctx=0x52d8c0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLDriverConnect

      symbol_peflctx=0x52f420

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLExecDirect

      symbol_peflctx=0x532ff0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLForeignKeys

      symbol_peflctx=0x534bc0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetConnectAttr

      symbol_peflctx=0x536670

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetDescField

      symbol_peflctx=0x538800

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetDescRec

      symbol_peflctx=0x538ea0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetDiagField

      symbol_peflctx=0x53a350

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetDiagRec

      symbol_peflctx=0x53b220

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetInfo

      symbol_peflctx=0x53cbd0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetStmtAttr

      symbol_peflctx=0x53cf60

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLGetTypeInfo

      symbol_peflctx=0x53dc40

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLPrepare

      symbol_peflctx=0x53fce0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLPrimaryKeys

      symbol_peflctx=0x540340

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLProcedureColumns

      symbol_peflctx=0x540ae0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLProcedures

      symbol_peflctx=0x541300

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLSetConnectAttr

      symbol_peflctx=0x542270

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLSetStmtAttr

      symbol_peflctx=0x546050

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLSetDescField

      symbol_peflctx=0x5440d0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLStatistics

      symbol_peflctx=0x548140

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Entered hgolofns at 2014/05/15-15:59:19

      libname=/usr/local/unixodbc/lib/libodbc.so, funcname=SQLTables

      symbol_peflctx=0x5491b0

      hoaerr:0

      Exiting hgolofns at 2014/05/15-15:59:19

      Exiting hgolofn, rc=0 at 2014/05/15-15:59:19

      HOSGIP for "HS_OPEN_CURSORS" returned "50"

      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"

      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"

      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"

      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"

      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"

      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"

      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"

      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"

      Invalid value of 32 given for HS_FDS_SQLLEN_INTERPRETATION

      treat_SQLLEN_as_compiled = 1

      Exiting hgoinit, rc=0 at 2014/05/15-15:59:19

      hostmstr:          0: HOA After hoainit

      hostmstr:          0: HOA Before hoalgon

      Entered hgolgon at 2014/05/15-15:59:19

      reco:0, name:postgres, tflag:0

      Entered hgosuec at 2014/05/15-15:59:19

      Exiting hgosuec, rc=0 at 2014/05/15-15:59:19

      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"

      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"

      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"

      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"

      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"

      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

      using postgres as default value for "HS_FDS_DEFAULT_OWNER"

      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

      Entered hgocont at 2014/05/15-15:59:19

      HS_FDS_CONNECT_INFO = "PostgreSQL"

      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

      Entered hgogenconstr at 2014/05/15-15:59:19

      dsn:PostgreSQL, name:postgres

      optn:

      Entered hgocip at 2014/05/15-15:59:19

      dsn:PostgreSQL

      Exiting hgocip, rc=0 at 2014/05/15-15:59:19

      ##>Connect Parameters (len=40)<##

      ## DSN=PostgreSQL;

      #! UID=postgres;

      #! PWD=*

      Exiting hgogenconstr, rc=0 at 2014/05/15-15:59:19

      Entered hgopoer at 2014/05/15-15:59:19

      hgopoer, line 231: got native error 202 and sqlstate 08001; message follows...

      [unixODBC]connction string lacks some options {08001,NativeErr = 202}

      Exiting hgopoer, rc=0 at 2014/05/15-15:59:19

      hgocont, line 2754: calling SqlDriverConnect got sqlstate 08001

      Exiting hgocont, rc=28500 at 2014/05/15-15:59:19 with error ptr FILE:hgocont.c LINE:2774 FUNCTION:hgocont() ID:Something other than invalid authorization

      Exiting hgolgon, rc=28500 at 2014/05/15-15:59:19 with error ptr FILE:hgolgon.c LINE:801 FUNCTION:hgolgon() ID:Calling hgocont

      hostmstr:          0: HOA After hoalgon

      RPC Calling nscontrol(0), rc=0

      hostmstr:          0: RPC Before Exit Agent

      hostmstr:          0: HOA Before hoaexit

      Entered hgoexit at 2014/05/15-15:59:19

      Exiting hgoexit, rc=0

      hostmstr:          0: HOA After hoaexit

      hostmstr:          0: RPC After Exit Agent


      ODBC Trace:

      [ODBC][6580][1400140759.882270][SQLSetConnectAttr.c][396]

        Entry:

        Connection = 0x90bd960

        Attribute = SQL_ATTR_AUTOCOMMIT

        Value = (nil)

        StrLen = -5

      [ODBC][6580][1400140759.882326][SQLSetConnectAttr.c][671]

        Exit:[SQL_SUCCESS]

      [ODBC][6580][1400140759.882642][SQLDriverConnect.c][728]

        Entry:

        Connection = 0x90bd960

        Window Hdl = (nil)

        Str In = [DSN=PostgreSQL;UID=postgres;PWD=********][length = 40]

        Str Out = 0x90bd200

        Str Out Max = 1024

        Str Out Ptr = 0xbfd1e25c

        Completion = 0

        UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

       

       

        DIAG [08001] connction string lacks some options

       

       

      [ODBC][6580][1400140759.887954][SQLDriverConnect.c][1454]

        Exit:[SQL_ERROR]

      [ODBC][6580][1400140759.888032][SQLGetDiagRec.c][680]

        Entry:

        Connection = 0x90bd960

        Rec Number = 1

        SQLState = 0xbfd1df58

        Native = 0xbfd1df7c

        Message Text = 0xbfd1dd20

        Buffer Length = 510

        Text Len Ptr = 0xbfd1df94

      [ODBC][6580][1400140759.888065][SQLGetDiagRec.c][717]

        Exit:[SQL_SUCCESS]

        SQLState = 08001

        Native = 0xbfd1df7c -> 202

        Message Text = [[unixODBC]connction string lacks some options]

      [ODBC][6580][1400140759.888108][SQLGetDiagRec.c][680]

        Entry:

        Connection = 0x90bd960

        Rec Number = 2

        SQLState = 0xbfd1df58

        Native = 0xbfd1df7c

        Message Text = 0xbfd1dd20

        Buffer Length = 510

        Text Len Ptr = 0xbfd1df94

      [ODBC][6580][1400140759.888136][SQLGetDiagRec.c][717]

        Exit:[SQL_NO_DATA]

      [ODBC][6580][1400140759.888222][SQLDisconnect.c][208]

        Entry:

        Connection = 0x90bd960

      [ODBC][6580][1400140759.888250][SQLDisconnect.c][237]Error: 08003

      [ODBC][6580][1400140759.888319][SQLFreeHandle.c][284]

        Entry:

        Handle Type = 2

        Input Handle = 0x90bd960

      [ODBC][6580][1400140759.888352][SQLFreeHandle.c][333]

        Exit:[SQL_SUCCESS]

      [ODBC][6580][1400140759.889499][SQLFreeHandle.c][219]

        Entry:

        Handle Type = 1

        Input Handle = 0x90abf70

       

      Thank you.

        • 1. Re: [Oracle to PostgreSQL]connction string lacks some options
          Mkirtley-Oracle

          Hi,

            Thanks for the information but what was the SQL you issued from SQLPLUS when you got the error ?

           

          Also, did you paste the complete tnsnames.ora ?  You posted -

           

          PostgreSQL =

          (DESCRIPTION =

             (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))

             (CONNECT_DATA =(SID = PostgreSQL)

             )

            (HS =OK)

           

          but it is missing the last bracket so should be -

           

          PostgreSQL =

          (DESCRIPTION =

             (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))

             (CONNECT_DATA =(SID = PostgreSQL)

             )

            (HS =OK)

            )

           

          Regards,

          Mike

          • 2. Re: [Oracle to PostgreSQL]connction string lacks some options
            2671659

            Hi,

                 Thank you for reminding.

            when I copy the tnsnames.ora content,I lose the last bracket.

            And I using select * from "testtbl"@k;

            l got the error message.

            • 3. Re: [Oracle to PostgreSQL]connction string lacks some options
              Mkirtley-Oracle

              Hi,

                Thanks for that.  How did you define the database link ?
              Did you define a user/password -

               

              create database link k connect to "userid" identified by "password" using 'PostgreSQL';

               

              Regards,

              Mike

              • 4. Re: [Oracle to PostgreSQL]connction string lacks some options
                Mkirtley-Oracle

                Okay - it looks like you did but can you confirm ?

                • 5. Re: [Oracle to PostgreSQL]connction string lacks some options
                  2671659

                  I done.I had created a database link k.

                   

                  SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_type='DATABASE LINK';

                  OWNER     OBJECT_NAME     OBJECT_TYPE     STATUS

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

                  SYS     K     DATABASE LINK     VALID

                   

                  [oracle@db_test bin]$ tnsping PostgreSQL

                  Used parameter files:

                  Used TNSNAMES adapter to resolve the alias

                  Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521)) (CONNECT_DATA =(SID = PostgreSQL)) (HS =OK))

                  OK (10 msec)

                  • 6. Re: [Oracle to PostgreSQL]connction string lacks some options
                    Kgronau-Oracle

                    The ODBC trace shows:

                    [ODBC][6580][1400140759.882642][SQLDriverConnect.c][728]

                      Entry:

                      Connection = 0x90bd960

                      Window Hdl = (nil)

                      Str In = [DSN=PostgreSQL;UID=postgres;PWD=********][length = 40]

                      Str Out = 0x90bd200

                      Str Out Max = 1024

                      Str Out Ptr = 0xbfd1e25c

                      Completion = 0

                      UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

                     

                    The DSn passed to the driver manager is correct, but the encoding looks strange.

                     

                    What's the unixODBC Driver version you are using (odbcinst -j) and did you compile it from the sources?

                    - Klaus

                    • 7. Re: [Oracle to PostgreSQL]connction string lacks some options
                      2671659

                      Hi,kgronau!

                       

                      unixODBC 2.3.2

                      DRIVERS............: /usr/local/unixodbc/etc/odbcinst.ini

                      SYSTEM DATA SOURCES: /usr/local/unixodbc/etc/odbc.ini

                      FILE DATA SOURCES..: /usr/local/unixodbc/etc/ODBCDataSources

                      USER DATA SOURCES..: /root/.odbc.ini

                      SQLULEN Size.......: 4

                      SQLLEN Size........: 4

                      SQLSETPOSIROW Size.: 2

                      • 8. Re: [Oracle to PostgreSQL]connction string lacks some options
                        Kgronau-Oracle

                        Where did you get that driver from? It is a 64bit ODBC driver not following the 64bit ODBC standard - your driver uses 32bit standard on 64bit platforms:

                        SQLULEN Size.......: 4

                        SQLLEN Size........: 4

                        SQLSETPOSIROW Size.: 2

                        A 64bit driver supporting the 64bit ODBC standard should report something similar to:

                        SQLULEN Size.......: 8

                        SQLLEN Size........: 8

                        SQLSETPOSIROW Size.: 8

                         

                        You can give it a try by adding to the gateway ini file the parameter:

                        HS_FDS_SQLLEN_INTERPRETATION=32

                         

                        Then start a new SQL*Plus session and test if that works. But I would strongly recommend you to get from unixODBC.org the source file of the ODBC Administrator and compile it from the source. Make sure to set the environment variables

                        export CPPFLAGS="-DSIZEOF_LONG_INT=8"

                        export CFLAGS="-DBUILD_REAL_64_BIT_MODE"

                        export CFLAGS="-DBUILD_REAL_64_BIT_MODE"

                         

                        before starting to build the driver manager.

                        - Klaus

                        • 9. Re: [Oracle to PostgreSQL]connction string lacks some options
                          2671659

                          Hi,kgronau!

                          I'm late!!I get the unixODBC driver from unixODBC.org.My platform is 32bit (uname -a)

                           

                          I try to adding the parameter HS_FDS_SQLLEN_INTERPRETATION,then it also not work.

                          • 10. Re: [Oracle to PostgreSQL]connction string lacks some options
                            Kgronau-Oracle

                            That parameter HS_FDS_SQLLEN_INTERPRETATION has no impact in your 32bit env - it is only available for a 64bit DG4ODBC when the ODBC driver manager/ODBC driver not 64bit compatible. So please remove that parameter.


                            So let's create a strace file to see if we get more details from this trace.

                            1. get the process id of the Oracle listener that spawns the gateway (ps -ef|grep tns)

                            2. strace -fae -o dg4odbc.log -p <gateway listener process id>

                            3. Now start a new SQL*Plus session and try to select from the  Postgres database again. Once you get the error, cancel the strace process from step 2 and upload the dg4odbc.log file to a public file share.

                            - Klaus

                            • 11. Re: [Oracle to PostgreSQL]connction string lacks some options
                              2671659

                              Hi,kgronau!

                                   This is the dg4odbc log file download link

                              dg4odbc.log download - 2share

                               

                              Thank you!

                              • 12. Re: [Oracle to PostgreSQL]connction string lacks some options
                                Kgronau-Oracle

                                When looking at the strace file the Driver Manager tries to load the odbc.ini you specified in your gateway init file: set ODBCINI=/etc/odbc.ini

                                but that fails as the oracle user can not access it - instead it tries to load a different file:

                                17811 open("/etc/odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = -1 EACCES (Permission denied)

                                17811 open("/usr/local/unixodbc/etc/odbc.ini", O_RDONLY) = 8

                                 

                                Could you please make sure that the oracle user can access that file?

                                You also mentioned at the beginning that you could connect to the Postgres database using isql - did you do the test as "oracle" user and what ENV parameters have you set before opening isql?

                                 

                                Just a minor issue which has no impact as your LD_LIBRARY_PATH in the environment is set correctly - the listener.ora file contains the line

                                (EVNS=LD_LIBRARY_PATH=/usr/local/unixodbc/lib:/usr/local/pgsqlodbc/lib:/u01/app/oracle/product/11.2.0/dbhome_1/bin)

                                but EVNS should be ENVS or even better ENV. So please correct:

                                (EVNS=LD_LIBRARY_PATH=/usr/local/unixodbc/lib:/usr/local/pgsqlodbc/lib:/u01/app/oracle/product/11.2.0/dbhome_1/bin)

                                to

                                (ENV="LD_LIBRARY_PATH=/usr/local/unixodbc/lib:/usr/local/pgsqlodbc/lib:/u01/app/oracle/product/11.2.0/dbhome_1/bin")

                                Then stop and start the listener.

                                 

                                - Klaus

                                • 13. Re: [Oracle to PostgreSQL]connction string lacks some options
                                  2671659

                                  Hi,Kgronau!

                                  This is useful for me.

                                  Thank you.