Hi folks,
I had configured an dblink between my Oracle 11g and Postgres 8.3, I can test with isql and it works just fine. Also I created on the postgres db an empty table, which I can select without any errors, since there is no data, however when I select from an table that contains data, I get this error:
ORA-28528: Erro de conversão de tipo de dados de Serviços Heterogêneos
ORA-02063: precedendo line a partir de HO.WORLD
28528. 00000 - "Heterogeneous Services datatype conversion error"
*Cause: Either an Oracle datatype could not be converted to a non-Oracle
datatype, or a non-Oracle datatype could not be converted
to an Oracle datatype. The following are possible reasons for
for the conversion failure:
-- overflow problems (in the case of numbers)
-- length limitations (in the case of character strings)
-- invalid values passed into the conversion routines
If any of you guys can help me with that, or have any suggestions please let me know.
ENCODING:
POSTGRES client and server encoding: LATIN1
Oracle:
| NLS_LANGUAGE | BRAZILIAN PORTUGUESE |
| NLS_TERRITORY | BRAZIL |
| NLS_CURRENCY | R$ |
| NLS_ISO_CURRENCY | BRAZIL |
| NLS_NUMERIC_CHARACTERS | ,. |
| NLS_CHARACTERSET | AL32UTF8 |
| NLS_CALENDAR | GREGORIAN |
| NLS_DATE_FORMAT | DD/MM/RR |
| NLS_DATE_LANGUAGE | BRAZILIAN PORTUGUESE |
| NLS_SORT | WEST_EUROPEAN |
| NLS_TIME_FORMAT | HH24:MI:SSXFF |
| NLS_TIMESTAMP_FORMAT | DD/MM/RR HH24:MI:SSXFF |
| NLS_TIME_TZ_FORMAT | HH24:MI:SSXFF TZR |
| NLS_TIMESTAMP_TZ_FORMAT | DD/MM/RR HH24:MI:SSXFF TZR |
| NLS_DUAL_CURRENCY | Cr$ |
| NLS_COMP | BINARY |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| NLS_RDBMS_VERSION | 11.2.0.3.0 |
--My configs:
hs/admin: initPGLINK.ora
HS_FDS_CONNECT_INFO = PGLINK
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME=/usr/pgsql-9.3/lib/psqlodbc.so
HS_LANGUAGE = "BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252"
set ODBCINI=/etc/odbc.ini
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledev.mx)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=PGLINK)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledev.mx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev.mx)
)
)
PRODEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledev.mx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodev.mx)
)
)
PGLINK =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST = oracledev.mx)(PORT = 1521))
(CONNECT_DATA=(SID_NAME = PGLINK))
(HS=OK)
)
about the dblink:
I´ve created it with this command:
create public database link pglink connect to "postgres" identified by "xyzyxyzyzyz" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev.mx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PGLINK))(HS=OK))' ;
because using:
create database link PGLINK connect to "pguser" identified by "pgpass" using 'PGLINK';
when testing, it doesn´t work. it gives this error:
ORA-28545: erro diagnosticado pelo Net8 ao conectar com um agente
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: precedendo 2 lines a partir de PGLINK
28545. 0000 - "error diagnosed by Net8 when connecting to an agent"
*Cause: An attempt to call an external procedure or to issue SQL
to a non-Oracle system on a Heterogeneous Services database link
failed at connection initialization. The error diagnosed
by Net8 NCR software is reported separately.
*Action: Refer to the Net8 NCRO error message. If this isn't clear,
check connection administrative setup in tnsnames.ora
and listener.ora for the service associated with the
Heterogeneous Services database link being used, or with
'extproc_connection_data' for an external procedure call.
Thanks!