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.

[Oracle to PostgreSQL]connction string lacks some options

2671659May 15 2014 — edited May 28 2014

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"