Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Why is my connection from Oracle 12c to PostgreSQL not working?

user2783794Oct 20 2015 — edited Oct 23 2015

My connection from Oracle 12c to PostgreSQL is returning the following:

select * from "test"@PG_LINK

ERROR at line 1:

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 PG_LINK

Here is my setup. Anyone have a time to take a look?

.odbc.ini

[ODBC Data Sources]

PG_LINK=PostgreSQL

[PG_LINK]

Debug=255

CommLog=1

ReadOnly=no

Driver=/usr/pgsql-9.4/lib/psqlodbcw.so

Servername=<remove IP address>

FetchBufferSize=99

Username=csm_test

Password=<removed>

Port=5432

Database=postgres

TRACE=Yes

TraceFile=/tmp/sql.log

Protocol=7.4

sslmode=disable

[Default]

Driver=/usr/lib64/liboplodbcS.so.1

initPG_LINK.ora

HS_FDS_CONNECT_INFO = PG_LINK

HS_FDS_TRACE_LEVEL = 255

HS_FDS_SHAREABLE_NAME=/ora/dba/work/unixODBC/lib/libodbc.so

HS_LANGUAGE=american_america.we8iso8859p1

HS_NLS_NCHAR=UCS2

set ODBCINI=/u01/app/oracle/.odbc.ini

Generated trace file: PG_LINK_agt_13870.trc

Oracle Corporation --- TUESDAY OCT 20 2015 15:04:11.308

Heterogeneous Agent Release

12.1.0.2.0

Oracle Corporation --- TUESDAY OCT 20 2015 15:04:11.307

Version 12.1.0.2.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"

HOSGIP returned value of "UCS2" for HS_NLS_NCHAR

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;gtw$:OPTTables;gtw$:OPTColumns;gtw$:OPTPrimaryKeys;gtw$:OPTForeignKeys;gtw$:OPTProcedures;gtw$:OPTStatistics"

setting HS_FDS_DELAYED_OPEN to default of "TRUE"

setting HS_FDS_WORKAROUNDS to default of "0"

setting HS_WORKAROUNDS to default of "0"

Exiting hgosdip, rc=0

ORACLE_SID is "PG_LINK"

Product-Info:

Port Rls/Upd:2/0 PrdStat:0

Agent:Oracle Database Gateway for ODBC

Facility:hsa

Class:ODBC, ClassVsn:12.1.0.2.0_0023, Instance:PG_LINK

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

HOCXU_DB_CSET=873

HS_LANGUAGE is american_america.we8iso8859p1

rc=32512 attempting to get LANG environment variable.

HOCXU_SEM_VER=121000

HOCXU_VC2_MAX=4000

HOCXU_RAW_MAX=2000

Entered hgolofn at 2015/10/20-15:04:11

HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/ora/dba/work/unixODBC/lib/libodbc.so"

Entered hgolofns at 2015/10/20-15:04:11

libname=/ora/dba/work/unixODBC/lib/libodbc.so, funcname=SQLAllocHandle

symbol_peflctx=0xdc741040

hoaerr:0