Error Ora-28500 and Sqlstate I or Displays a Square Bracket - [ - Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager

Symptom:

 SQL> select * from "t"@pglink;

select * from "t"@pglink

                *

ERROR at line 1:

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

[

Log file: /u01/db/hs/log/pgdsn_agt_15012.trc:

Exiting hgocont, rc=28500 at 2021/12/23-14:57:07 with error ptr FILE:hgocont.c LINE:2842 ID:Something other than invalid authorization

Cause:

The Oracle RDBMS is using a Unicode characterset:

select * from NLS_DATABASE_PARAMETERS ; 

PARAMETER                     VALUE 
------------------------      ----------------------------------------
NLS_CHARACTERSET              AL32UTF8

Solution:

Add the following to the DG4ODBC parameter file init<dg4odbc>.ora -

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

[oracle@ip-10-2-2-192 admin]$ cat /u01/db/hs/admin/initpgdsn.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgdsn

HS_FDS_TRACE_LEVEL = DEBUG

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# Avoid "[" displayed after "ORA-28500"

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini