13 Replies Latest reply: Aug 9, 2013 1:09 PM by Kodali.S RSS

    after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work

    Kodali.S

      we have applied some patches and upgraded from 11.2 to 11.2.0.3 recently.

       

      Now if i try to query the postgres db using the dblink it always returns

       

      ORA-00942: table or view does not exist

      [OpenLink][ODBC][PostgreSQL Server] Table or view not found {42502}

      ORA-02063: preceding 2 lines from TEST12

        • 1. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
          Kodali.S

          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 64 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 "pgsqlname"

          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:pgsqlname

          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=31

          HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1

          LANG=en_US.UTF-8

          HOCXU_SEM_VER=112000

          Entered hgolofn at 2013/07/31-15:55:00

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

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab20e80

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab191e0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab15a00

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab107b0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab08150

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab05e90

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1ab60

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab08f40

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab0ac80

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab20410

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab117c0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab0a6f0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab200b0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab12590

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab0a1c0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab18740

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab08c40

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab08710

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab18880

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab20270

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1c410

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1c090

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab007b0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab05ce0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab18520

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab07dc0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab09ac0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaaffc20

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1e090

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1d7e0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1cbb0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab0f270

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab0dae0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab136c0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1ff20

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaafd250

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab16d80

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaafeed0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaafe550

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaafdad0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1e760

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1faa0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab1d330

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaaafb6f0

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Entered hgolofns at 2013/07/31-15:55:00

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

          symbol_peflctx=0xaab01d70

          hoaerr:0

          Exiting hgolofns at 2013/07/31-15:55:00

          Exiting hgolofn, rc=0 at 2013/07/31-15:55:00

          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 64 given for HS_FDS_SQLLEN_INTERPRETATION

          treat_SQLLEN_as_compiled = 1

          Exiting hgoinit, rc=0 at 2013/07/31-15:55:00

          hostmstr:          0:     HOA After hoainit

          hostmstr:          0:     HOA Before hoalgon

          Entered hgolgon at 2013/07/31-15:55:00

          reco:0, name:readonly, tflag:0

          Entered hgosuec at 2013/07/31-15:55:00

          Exiting hgosuec, rc=0 at 2013/07/31-15:55:00

          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 readonly as default value for "HS_FDS_DEFAULT_OWNER"

          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

          Entered hgocont at 2013/07/31-15:55:00

          HS_FDS_CONNECT_INFO = "pgsqlname"

          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

          Entered hgogenconstr at 2013/07/31-15:55:00

          dsn:pgsqlname, name:readonly

          optn:

          Entered hgocip at 2013/07/31-15:55:00

          dsn:pgsqlname

          Exiting hgocip, rc=0 at 2013/07/31-15:55:00

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

          ## DSN=pgsqlname;

          #! UID=readonly;

          #! PWD=*

          Exiting hgogenconstr, rc=0 at 2013/07/31-15:55:00

          Entered hgolosf at 2013/07/31-15:55:00

          ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFD7F 0x015F 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                        0x0000 0x0000 0xFA00 0x3F5F

          Exiting hgolosf, rc=0 at 2013/07/31-15:55:00

          DriverName:pgr7_mt_lt.so, DriverVer:06.02.1007 OpenLink PostgreSQL Lite Driver

          DBMS Name:PostgreSQL, DBMS Version:07.02.0000

          Exiting hgocont, rc=0 at 2013/07/31-15:55:00

          SQLGetInfo returns Y for SQL_CATALOG_NAME

          SQLGetInfo returns 32 for SQL_MAX_CATALOG_NAME_LEN

          Exiting hgolgon, rc=0 at 2013/07/31-15:55:00

          hostmstr:          0:     HOA After hoalgon

          RPC Calling nscontrol(0), rc=0

          hostmstr:          0: RPC Before Upload Caps

          hostmstr:          0:     HOA Before hoaulcp

          Entered hgoulcp at 2013/07/31-15:55:00

          Entered hgowlst at 2013/07/31-15:55:00

          Exiting hgowlst, rc=0 at 2013/07/31-15:55:00

          SQLGetInfo returns 0x0 for SQL_OWNER_USAGE

          TXN Capable:2, Isolation Option:0xa

          SQLGetInfo returns 32 for SQL_MAX_SCHEMA_NAME_LEN

          SQLGetInfo returns 32 for SQL_MAX_TABLE_NAME_LEN

          SQLGetInfo returns 32 for SQL_MAX_PROCEDURE_NAME_LEN

          HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER

          SQLGetInfo returns   (0x20) for SQL_IDENTIFIER_QUOTE_CHAR

          3 instance capabilities will be uploaded

            capno:1989, context:0x00000000, add-info:        0

            capno:1992, context:0x0001ffff, add-info:        0

            capno:3007, context:0x00000000, add-info:       -3, translation:"36"

          Exiting hgoulcp, rc=0 at 2013/07/31-15:55:00

          hostmstr:          0:     HOA After hoaulcp

          hostmstr:          0: RPC After Upload Caps

          hostmstr:          0: RPC Before Upload DDTR

          hostmstr:          0:     HOA Before hoauldt

          Entered hgouldt at 2013/07/31-15:55:00

          NO instance DD translations were uploaded

          Exiting hgouldt, rc=0 at 2013/07/31-15:55:00

          hostmstr:          0:     HOA After hoauldt

          hostmstr:          0: RPC After Upload DDTR

          hostmstr:          0: RPC Before Begin Trans

          hostmstr:          0:     HOA Before hoabegn

          Entered hgobegn at 2013/07/31-15:55:00

          tflag:0 , initial:1

          hoi:0x7171ec78, ttid (len 28) is ...

            00: 4D4D4D41 54524958 2E623835 34333234  [ORACLEDBNAME.b854324]

            10: 322E332E 32392E35 37323136           [2.3.29.57216]

                           tbid (len 25) is ...

            00: 4D4D4D41 54524958 5B332E32 392E3537  [ORACLEDBNAME[3.29.57]

            10: 3231365D 5B312E34 5D                 [216][1.4]]

          Exiting hgobegn, rc=0 at 2013/07/31-15:55:00

          hostmstr:          0:     HOA After hoabegn

          hostmstr:          0: RPC After Begin Trans

          hostmstr:          0: RPC Before Describe Table

          hostmstr:          0:     HOA Before hoadtab

          Entered hgodtab at 2013/07/31-15:55:00

          count:1

            table: TRIALS

          Allocate hoada[0] @ 0x1d4e0910

          Free hoada[0] @ 0x1d4e0910

          SQL text from hgodtab, id=0, len=22 ...

               00: 73656C65 6374202A 2066726F 6D202254  [select * from "T]

               10: 5249414C 5322                        [RIALS"]

          Entered hgopoer at 2013/07/31-15:55:00

          hgopoer, line 231: got native error 0 and sqlstate 42S02; message follows...

          [OpenLink][ODBC][PostgreSQL Server]Table or view not found {42S02}

          Exiting hgopoer, rc=0 at 2013/07/31-15:55:00

          Entered hgopdsc at 2013/07/31-15:55:00

          • 2. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
            Kgronau-Oracle

            Are you sure the table is called TRIALS in capital letters at your Postgres database?

            When you use a native ODBC test utility like isql, can you query the table TRIALS (in capital letters)? Can you post the isql output running the select?

             

            - Klaus

            • 3. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
              Kodali.S

              Hi Klaus,

               

              We are using openlink driver for postgres.

               

              if I use select * from "trials"@pgsqlname; or select * from "TRIALS"@pgsqlname or select * from trials@pgsqlname or select * from TRIALS@pgsqlname from the dev where the patch is not applied its working fine. and Test used to work fine untill we applied the patch.

               

              Now for any of the above query it returns the same message table ot view not found.

                  
              I could query using the iodbctest provided by Openlink to test the query and it works fine,


              SQL>select trial_id from TRIALS where trial_id = 15;

              trial_id
              -----------
              15

              result set 1 returned 1 rows.


              SQL>

              • 4. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                Mkirtley-Oracle

                Hi,

                  Did you change the database links used for the gateway ? Are you using the same user in the db link as before ? Does this user have the correct privileges on the Postgres table ?
                What happens if you try -

                 

                select * from "owner"."trials"@pgsqlname :

                 

                and replace "owner" with the Postgres owner - in the correct case.
                Can you confirm the exact case for the table in Postgres ? Is it "trials", "Trials", "TRIALS" or something else ? Use this case in the select statement in double quotes.

                 

                Regards,

                Mike

                • 5. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                  Kodali.S

                  Hi,

                  when the patch was applied for 11.2.0.3 the dbhome got changed, but we have changed the refrences to new dbhome.

                  I can get the table run from the iodbctest from openlink driver for postgres


                  SQL>select * from public.holidays;

                  holiday_id |holiday   |label
                  -----------+----------+--------------------------------
                  10         |2005-05-30|Memorial Day
                  11         |2005-07-04|Independence Day
                  12         |2005-09-05|Labor Day
                  13         |2005-11-24|Thanksgiving Day
                  17         |2006-05-29|Memorial Day
                  18         |2006-07-04|Independence Day
                  19         |2006-09-04|Labor Day
                  20         |2006-11-23|Thanksgiving
                  21         |2006-12-25|Christmas
                  22         |2007-01-01|New Year's Day
                  23         |2007-05-28|Memorial Day
                  24         |2007-07-04|Independence Day
                  25         |2007-09-03|Labor Day
                  26         |2007-11-22|Thanksgiving Day
                  27         |2007-12-25|Christmas Day
                  31         |2008-01-01|New Year's Day
                  32         |2008-05-26|Memorial Day
                  33         |2008-07-04|4th of July
                  34         |2008-09-01|Labor Day
                  35         |2008-11-27|Thanksgiving
                  36         |2008-12-25|Christmas

                  result set 1 returned 21 rows.

                   

                  this is the trace file.

                   

                  Oracle Corporation --- THURSDAY  AUG 01 2013 14:08:36.353


                  Heterogeneous Agent Release
                  11.2.0.3.0

                   


                  Oracle Corporation --- THURSDAY  AUG 01 2013 14:08:36.353

                      Version 11.2.0.3.0

                  Entered hgogprd
                  HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
                  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 64 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 "pgsqldb"
                  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:pgsqldb
                  Exiting hgogprd, rc=0
                  Entered hgoinit
                  HOCXU_COMP_CSET=1
                  HOCXU_DRV_CSET=31
                  HOCXU_DRV_NCHAR=873
                  HOCXU_DB_CSET=31
                  HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1
                  LANG=en_US.UTF-8
                  HOCXU_SEM_VER=112000
                  Entered hgolofn at 2013/08/01-14:08:36
                  HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/OpenLink/lib/libodbc.so"
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab20e80
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab191e0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab15a00
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab107b0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab08150
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab05e90
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1ab60
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab08f40
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab0ac80
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab20410
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab117c0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab0a6f0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab200b0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab12590
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab0a1c0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab18740
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab08c40
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab08710
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab18880
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab20270
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1c410
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1c090
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab007b0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab05ce0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab18520
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab07dc0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab09ac0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaaffc20
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1e090
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1d7e0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1cbb0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab0f270
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab0dae0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab136c0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1ff20
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaafd250
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab16d80
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaafeed0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaafe550
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaafdad0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1e760
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1faa0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab1d330
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaaafb6f0
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Entered hgolofns at 2013/08/01-14:08:36
                  symbol_peflctx=0xaab01d70
                  hoaerr:0
                  Exiting hgolofns at 2013/08/01-14:08:36
                  Exiting hgolofn, rc=0 at 2013/08/01-14:08:36
                  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 64 given for HS_FDS_SQLLEN_INTERPRETATION
                  treat_SQLLEN_as_compiled = 1
                  Exiting hgoinit, rc=0 at 2013/08/01-14:08:36
                  Entered hgolgon at 2013/08/01-14:08:36
                  reco:0, name:readonly, tflag:0
                  Entered hgosuec at 2013/08/01-14:08:36
                  Exiting hgosuec, rc=0 at 2013/08/01-14:08:36
                  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 readonly as default value for "HS_FDS_DEFAULT_OWNER"
                  HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                  Entered hgocont at 2013/08/01-14:08:36
                  HS_FDS_CONNECT_INFO = "pgsqldb"
                  RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                  Entered hgogenconstr at 2013/08/01-14:08:36
                  dsn:pgsqldb, name:readonly
                  optn:
                  Entered hgocip at 2013/08/01-14:08:36
                  dsn:pgsqldb
                  Exiting hgocip, rc=0 at 2013/08/01-14:08:36
                  Exiting hgogenconstr, rc=0 at 2013/08/01-14:08:36
                  Entered hgolosf at 2013/08/01-14:08:36
                  Exiting hgolosf, rc=0 at 2013/08/01-14:08:36
                  DriverName:pgr7_mt_lt.so, DriverVer:06.02.1007 OpenLink PostgreSQL Lite Driver
                  DBMS Name:PostgreSQL, DBMS Version:07.02.0000
                  Exiting hgocont, rc=0 at 2013/08/01-14:08:36
                  SQLGetInfo returns Y for SQL_CATALOG_NAME
                  SQLGetInfo returns 32 for SQL_MAX_CATALOG_NAME_LEN
                  Exiting hgolgon, rc=0 at 2013/08/01-14:08:36
                  Entered hgoulcp at 2013/08/01-14:08:36
                  Entered hgowlst at 2013/08/01-14:08:36
                  Exiting hgowlst, rc=0 at 2013/08/01-14:08:36
                  SQLGetInfo returns 0x0 for SQL_OWNER_USAGE
                  TXN Capable:2, Isolation Option:0xa
                  SQLGetInfo returns 32 for SQL_MAX_SCHEMA_NAME_LEN
                  SQLGetInfo returns 32 for SQL_MAX_TABLE_NAME_LEN
                  SQLGetInfo returns 32 for SQL_MAX_PROCEDURE_NAME_LEN
                  HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
                  SQLGetInfo returns   (0x20) for SQL_IDENTIFIER_QUOTE_CHAR
                  3 instance capabilities will be uploaded
                    capno:1989, context:0x00000000, add-info:        0
                    capno:1992, context:0x0001ffff, add-info:        0
                    capno:3007, context:0x00000000, add-info:       -3, translation:"36"
                  Exiting hgoulcp, rc=0 at 2013/08/01-14:08:36
                  Entered hgouldt at 2013/08/01-14:08:36
                  NO instance DD translations were uploaded
                  Exiting hgouldt, rc=0 at 2013/08/01-14:08:36
                  Entered hgobegn at 2013/08/01-14:08:36
                  tflag:0 , initial:1
                  hoi:0x6ebdff78, ttid (len 28) is ...
                    00: 4D4D4D41 54524958 2E623835 34333234  [MORACLEDB.b854324]
                    10: 322E342E 32322E35 36373232           [2.4.22.56722]
                                   tbid (len 25) is ...
                    00: 4D4D4D41 54524958 5B342E32 322E3536  [MORACLEDB[4.22.56]
                    10: 3732325D 5B312E34 5D                 [722][1.4]]
                  Exiting hgobegn, rc=0 at 2013/08/01-14:08:36
                  Entered hgodtab at 2013/08/01-14:08:36
                  count:1
                    table: public.holidays
                  Allocate hoada[0] @ 0x198a89d0
                  Free hoada[0] @ 0x198a89d0
                  SQL text from hgodtab, id=0, len=31 ...
                       00: 73656C65 6374202A 2066726F 6D202270  [select * from "p]
                       10: 75626C69 632E686F 6C696461 797322    [ublic.holidays"]
                  Entered hgopoer at 2013/08/01-14:08:36
                  hgopoer, line 231: got native error 0 and sqlstate 42000; message follows...
                  [OpenLink][ODBC][PostgreSQL Server]Statement(s) could not be prepared {42000}[OpenLink][ODBC][PostgreSQL Server] relation "public.holidays" does not exist {HY000,NativeErr = 1}
                  Exiting hgopoer, rc=0 at 2013/08/01-14:08:36
                  Entered hgopdsc at 2013/08/01-14:08:36
                  Describing procedure public.holidays
                  Output hoada
                  hgopdsc, line 1434: NO hoada to print
                  Exiting hgopdsc, rc=942 at 2013/08/01-14:08:36
                  The hoada for table public.holidays follows...
                  hgodtab, line 1092: NO hoada to print
                  Exiting hgodtab, rc=0 at 2013/08/01-14:08:36
                  Entered hgopars, cursor id 1 at 2013/08/01-14:08:36
                  type:0
                  SQL text from hgopars, id=1, len=31 ...
                       00: 53454C45 4354202A 2046524F 4D202270  [SELECT * FROM "p]
                       10: 75626C69 632E686F 6C696461 797322    [ublic.holidays"]
                  Entered hgopoer at 2013/08/01-14:08:36
                  hgopoer, line 231: got native error 1 and sqlstate HY000; message follows...
                  [OpenLink][ODBC][PostgreSQL Server] current transaction is aborted, commands ignored until end of transaction block {HY000,NativeErr = 1}
                  Exiting hgopoer, rc=0 at 2013/08/01-14:08:36
                  hgopars, line 457: calling SQLPrepare got sqlstate HY000
                  Exiting hgopars, rc=28500 at 2013/08/01-14:08:36 with error ptr FILE:hgopars.c LINE:487 FUNCTION:hgopars() ID:Prepare stmt
                  Entered hgoroll at 2013/08/01-14:08:36
                  tflag:1 , cmt(0):
                  hoi:0x6ebdff70, ttid (len 28) is ...
                     00: 4D4D4D41 54524958 2E623835 34333234  [MORACLEDB.b854324]
                     10: 322E342E 32322E35 36373232           [2.4.22.56722]
                                   tbid (len 25) is ...
                     00: 4D4D4D41 54524958 5B342E32 322E3536  [MORACLEDB[4.22.56]
                     10: 3732325D 5B312E34 5D                 [722][1.4]]
                  Entered hgocpctx at 2013/08/01-14:08:36
                  Exiting hgocpctx, rc=0 at 2013/08/01-14:08:36
                  Exiting hgoroll, rc=0 at 2013/08/01-14:08:36
                  Entered hgolgof at 2013/08/01-14:09:51
                  tflag:1
                  Exiting hgolgof, rc=0 at 2013/08/01-14:09:51
                  Entered hgoexit at 2013/08/01-14:09:51
                  Exiting hgoexit, rc=0

                  • 6. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                    Kodali.S

                    If i use

                     

                    select * from "public"."holidays"@pgsqlname;

                    then isee the trace gets the column names but still the same error though...

                     

                      Entered hgodtab at 2013/08/01-14:38:18

                    count:1

                      table: public.holidays

                    Allocate hoada[0] @ 0x5ac09d0

                    Entered hgopcda at 2013/08/01-14:38:18

                    Column:1(holiday_id): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2013/08/01-14:38:18

                    Entered hgopcda at 2013/08/01-14:38:18

                    Column:2(holiday): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2013/08/01-14:38:18

                    Entered hgopcda at 2013/08/01-14:38:18

                    Column:3(label): dtype:12 (VARCHAR), prc/scl:32/0, nullbl:1, octet:0, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2013/08/01-14:38:18

                    The hoada for table public.holidays follows...

                    hgodtab, line 1092: Printing hoada @ 0x5ac09d0

                    MAX:3, ACTUAL:3, BRC:1, WHT=6 (TABLE_DESCRIBE)

                    hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)

                    DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      4 INTEGER N          4          4   0/  0    0   0   0 holiday_id

                    91 DATE    N         16         16   0/  0    0   0   0 holiday

                    12 VARCHAR Y          0          0   0/  0    0   0 200 label

                    Exiting hgodtab, rc=0 at 2013/08/01-14:38:18

                    Entered hgodafr, cursor id 0 at 2013/08/01-14:38:18

                    Free hoada @ 0x5ac09d0

                    Exiting hgodafr, rc=0 at 2013/08/01-14:38:18

                    Entered hgopars, cursor id 1 at 2013/08/01-14:38:18

                    type:0

                    SQL text from hgopars, id=1, len=74 ...

                         00: 53454C45 43542041 312E2268 6F6C6964  [SELECT A1."holid]

                         10: 61795F69 64222C41 312E2268 6F6C6964  [ay_id",A1."holid]

                         20: 6179222C 41312E22 6C616265 6C222046  [ay",A1."label" F]

                         30: 524F4D20 22707562 6C696322 2E22686F  [ROM "public"."ho]

                         40: 6C696461 79732220 4131               [lidays" A1]

                    Entered hgopoer at 2013/08/01-14:38:18

                    hgopoer, line 231: got native error 0 and sqlstate 42S02; message follows...

                    [OpenLink][ODBC][PostgreSQL Server]Table or view not found {42S02}

                    Exiting hgopoer, rc=0 at 2013/08/01-14:38:18

                    hgopars, line 457: calling SQLPrepare got sqlstate 42S02

                    Exiting hgopars, rc=942 at 2013/08/01-14:38:18

                    Entered hgoroll at 2013/08/01-14:38:18

                    tflag:1 , cmt(0):

                    hoi:0x8434e590, ttid (len 27) is ...

                       00: 4D4D4D41 54524958 2E623835 34333234  [ORACLEDB.b854324]

                       10: 322E392E 392E3539 313637             [2.9.9.59167]

                                     tbid (len 24) is ...

                       00: 4D4D4D41 54524958 5B392E39 2E353931  [ORACLEDB[9.9.591]

                       10: 36375D5B 312E345D                    [67][1.4]]

                    Entered hgocpctx at 2013/08/01-14:38:18

                    Exiting hgocpctx, rc=0 at 2013/08/01-14:38:18

                    Exiting hgoroll, rc=0 at 2013/08/01-14:38:18

                    • 7. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                      Kgronau-Oracle

                      Could you please test using a ODBC test utility (for example isql) which which select works for you:

                                  SELECT A1."holiday_id",A1."holiday",A1."label" FROM "public"."holidays" A1

                      or

                              SELECT A1.holiday_id,A1.holiday,A1.label FROM public.holidays A1

                      or

                             

                      SELECT A1.'holiday_id',A1.'holiday',A1.'label' FROM 'public'.'holidays' A1

                      For the third statement please test using a back tick or a single quote.

                      • 8. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                        Kodali.S

                        [oracle@oracledb-test bin]$ ./iodbctest
                        iODBC Demonstration program
                        This program shows an interactive SQL processor
                        Driver Manager: 03.52.0710.0904

                        Enter ODBC connect string (? shows list): DSN=pgsqldb
                        Driver: 08.04.0200 (psqlodbcw.so)

                        SQL>SELECT A1."holiday_id",A1."holiday",A1."label" FROM "public"."holidays" A1;

                        holiday_id |holiday   |label
                        -----------+----------+--------------------------------
                        10         |2005-05-30|Memorial Day
                        11         |2005-07-04|Independence Day
                        12         |2005-09-05|Labor Day
                        13         |2005-11-24|Thanksgiving Day
                        17         |2006-05-29|Memorial Day
                        18         |2006-07-04|Independence Day
                        19         |2006-09-04|Labor Day
                        20         |2006-11-23|Thanksgiving
                        21         |2006-12-25|Christmas
                        22         |2007-01-01|New Year's Day
                        23         |2007-05-28|Memorial Day
                        24         |2007-07-04|Independence Day
                        25         |2007-09-03|Labor Day
                        26         |2007-11-22|Thanksgiving Day
                        27         |2007-12-25|Christmas Day
                        31         |2008-01-01|New Year's Day
                        32         |2008-05-26|Memorial Day
                        33         |2008-07-04|4th of July
                        34         |2008-09-01|Labor Day
                        35         |2008-11-27|Thanksgiving
                        36         |2008-12-25|Christmas

                        result set 1 returned 21 rows.

                         


                        SQL>SELECT A1.holiday_id,A1.holiday,A1.label FROM public.holidays A1;

                        holiday_id |holiday   |label
                        -----------+----------+--------------------------------
                        10         |2005-05-30|Memorial Day
                        11         |2005-07-04|Independence Day
                        12         |2005-09-05|Labor Day
                        13         |2005-11-24|Thanksgiving Day
                        17         |2006-05-29|Memorial Day
                        18         |2006-07-04|Independence Day
                        19         |2006-09-04|Labor Day
                        20         |2006-11-23|Thanksgiving
                        21         |2006-12-25|Christmas
                        22         |2007-01-01|New Year's Day
                        23         |2007-05-28|Memorial Day
                        24         |2007-07-04|Independence Day
                        25         |2007-09-03|Labor Day
                        26         |2007-11-22|Thanksgiving Day
                        27         |2007-12-25|Christmas Day
                        31         |2008-01-01|New Year's Day
                        32         |2008-05-26|Memorial Day
                        33         |2008-07-04|4th of July
                        34         |2008-09-01|Labor Day
                        35         |2008-11-27|Thanksgiving
                        36         |2008-12-25|Christmas

                        result set 1 returned 21 rows.


                        SQL>select A1.'holiday_id',A1.'holiday',A1.'label' FROM public.'holidays' A1;
                        1: SQLExec = ERROR: syntax error at or near "'holiday_id'";
                        Error while executing the query (7) SQLSTATE=42601
                        1: SQLExec = ERROR: syntax error at or near "'holiday_id'" (110) SQLSTATE=42601

                        • 9. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                          Mkirtley-Oracle

                          Hi,

                            Is the user in the database link used by Oracle the same as that used for the iodbctest connection ?

                           

                          From Oracle what happens if you try -

                           

                          SELECT A1."holiday_id",A1."holiday",A1."label" FROM "public"."holidays"@pgsqlname A1;

                           

                          Regards,

                          Mike

                          • 10. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                            Kgronau-Oracle

                            In the gateway trace the quoted identifier parameter is set to true (HS_FDS_QUOTE_IDENTIFIER to default of "TRUE") and according to the tests you did the ODBC driver accepts double quotes as quoted identifier. Have you set in your odbc.ini the parameter QuotedId=Yes?


                            Didn't yet check if HS_FDS_QUOTE_IDENTIFIER=FALSE works for Postgres, but it might be also worth to check when you set it in the gateway init file if selects then work.

                             

                            - Klaus


                            • 11. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                              Kodali.S

                              Hi,

                               

                              In the openlink.ini

                               

                              file we have the followinf config

                               

                               

                              [Environment PostgreSQL]

                               

                              PGHOST = db-test

                               

                              PGPORT = 5432

                               

                              CURSOR_SENSITIVITY = LOW ; Set to HIGH after loading oplrvc.sql

                               

                              NO_QUOTED_IDENTIFIERS = N

                               

                              HSODBC_FIX = N ; Set to Y when using HSODBC

                               

                              If I set the NO_QUOTED_IDENTIFIERS = Y its not working. but previous when in 11.2.0 it used work not with 11.2.0.3 of oracle version.

                               

                              The code is like this initially when in oracle 10g and it worked fine.
                              select * from "schemaname.table1"@dblinkname;

                              Then when we have upgraded to oracle 11g rel2
                              This schema prefix when referred as "schemaname.table1" wasnot working. And when referred as "schemaname"."table1"@dblinkname was working.
                              so openlink provided a fix to the driver as we did not want to change the code everywhere we referred the schema.


                              Now we have release 11.2.0.3 and its not working again with the fresh driver install
                              Not working -- select * from " schemaname.table1"@dblinkname;
                              Working -- select * from " schemaname"."table1"@dblinkname;

                              • 12. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                                Mkirtley-Oracle

                                Hi,

                                  The gateway has never supported the syntax -

                                 

                                select * from "schemaname.table1"@dblinkname ;


                                If Openlink gave you a patched driver to allow this then you will need to follow up with them if it no longer works.


                                Regards,

                                Mike

                                • 13. Re: after Upgrade to 11.2.0.3 dblink to Postgres using openlink driver doesnot work
                                  Kodali.S

                                  setting this in init*.ora file HS_FDS_QUOTE_IDENTIFIER to "FALSE"

                                  and in openlink.ini file setting   NO_QUOTED_IDENTIFIERS = N

                                  [Environment PostgreSQL]

                                    PGHOST                = db-test

                                    PGPORT                = 5432

                                    CURSOR_SENSITIVITY    = LOW       ; Set to HIGH after loading oplrvc.sql

                                    NO_QUOTED_IDENTIFIERS = N

                                    HSODBC_FIX            = N         ; Set to Y when using HSODBC

                                   

                                  both this combination worked for the following statment so we need not change our code for schema prefix

                                   

                                  select * from "schemaname.table1"@dblinkname ; this is working now with above settings.