Oracle Database Discussions

Dharma,
If the isql connection doesn't work then the DG4ODBC conenction will never work either.
There is a problem with the basic ODBC setup which needs to be resolved.
You should really follow this up with the supplier of the ODBC driver as there is no Oracle software involved in the ISQL connection.
However, post the odbc.ini file used for the setup and we may be able to help.

As the error message says - there is a problem with the conenction to the SQL*Server database so you need to check the details in the odbc.ini file are correct.

Regards,
Mike

Hai mike,

thanks for replay mike,

Now isql error resolved,but i cant query any sql serve table uning dblink,please refer below error,

[oradev@testebs bin]$ isql -v dblink4sqlserver sa nilgiris
---------------------------------------
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
---------------------------------------
SQL> desc store@ORACLE_SQL;
[37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
[37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.
[ISQL]ERROR: Could not SQLExecute
SQL> select *from store@ORACLE_SQL;
[S0002][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'store@ORACLE_SQL'.
[37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
[ISQL]ERROR: Could not SQLExecute


Above error mention invalid object name,but i checked that at sqlserver,that table will be there.

And i query Sqlplus,it will show different error,

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select *from store@ORACLE_SQL;
select *from store@ORACLE_SQL
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))(CONNECT_
DATA=(SID=BACKOFF)))
ORA-02063: preceding line from ORACLE_SQL
Process ID: 14054
Session ID: 2900 Serial number: 10756


Please guide me to solve above error

Regards
Dharma

Hi Dharma,
From ISQL are you trying to use an Oracle database link -

SQL> desc store@ORACLE_SQL;
[37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
[37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.
[ISQL]ERROR: Could not SQLExecute
SQL> select *from store@ORACLE_SQL;

If so, that isn't supported.
From isql you should be able to do -

describe store
select * from store ;

as you are already connected to the SQL*Server database.
SQL*Server names are case sensitive so you may need to do -

select * from "store" ;

The SQLPLUS ORA-28511 error can have different causes so we need to see the configuration files -

- gateway listener.ora
- gateway initBACKOFF.ora
- tnsnames.ora
- create database link statement (hash out the password)

and also a 255 level gateway trace.
From the errors the gateway SID looks to be BACKOFF.

In initBACKOFF.ora set the following -

HS_FDS_TRACE_LEVEL=255

and run a select from a new SQLPLUS session -

select * from "store"@oracle_sql ;

then end the session.
The trace will be in the gateway $ORACLE_HOME/hs/log - on Linux/Unix. On Windows it is in $ORACLE_HOME/hs/trace.

If the trace is large either upload to somewhere offsite where it is accessible or post the later section which should show some errors.

Regards,
Mike

Hai mike

Thank for replay,

Now i got these error

SQL> select *from "store"@ORACLE_SQL;
select *from "store"@ORACLE_SQL
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


1)Gateway listener.ora

SID_LIST_DEV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0)
(SID_NAME = DEV)
)
(SID_DESC=
(SID_NAME = NILGDB2)
(ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0/)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH = /Oracle/DBlink_RPM/ODBC_Driver/odbc_cli/clidriver/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
)
(SID_DESC=
(SID_NAME=backoff)
(ORACLE_HOME=/Oracle/CRP/DEV/db/tech_st/11.1.0/)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/Oracle/DBlink_RPM/ODBC_Driver/sqlncli-11.0.1790.0/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
)
)


2)tnsnames.ora


backoff=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))
(CONNECT_DATA =
(SID = backoff)
)
(HS=OK)
)


3)initBACKOFF.ora

HS_FDS_CONNECT_INFO = dblink4sqlserver
HS_FDS_TRACE_LEVEL=255
HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_FDS_CONNECT_PROPERTIES='120'


set ODBCINI=/usr/local/etc/odbc.ini

4)Dblink

Create public database link ORACLE_SQL connect to "erp" identified by "123456789" using 'backoff';

Please refer above error,and trace file not generated

Regards
Dharma

Dharma,
The TNS-12154 (ORA-12154) means that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
In tnsnames.ora and listener.ora you refer to the gateway sid as 'backoff' but the init file is called initBACKOFF.ora.
Can you change the name to upper case in both files, stop and start the lsitener and try again ?

Also, you have given the full listener details but I assume 192.168.2.14 is the hsot where the listener is running ?

You won't get a gateway trace file because you are not getting as far as starting the gateway executable.
One other point, you should comment out the like -

HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt

and leave the trace name to the default name, which will be backoff_agt_xxxx.trc - where xxxx is the gateway pid number and will change each time you start a gateway executable.

Regards,
Mike

Dear Mike,

I have changed previously as you mention,but li got different error,


SQL> create public database link ORACLE_SQL connect to "sa" identified by "nilgiris" using 'BACKOFF';

Database link created.

SQL> select *from "store"@ORACLE_SQL;
select *from "store"@ORACLE_SQL
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))(CONNECT_
DATA=(SID=BACKOFF)))
ORA-02063: preceding line from ORACLE_SQL
Process ID: 7584
Session ID: 2843 Serial number: 1149


Please refer my configuration below,



*******sqlnet.ora***********

NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
SQLNET.INBOUND_CONNECT_TIMEOUT= 120
DIAG_ADR_ENABLED=ON
ADR_BASE=/Oracle/CRP/DEV/db/tech_st/11.1.0/admin/DEV_testebs
SEC_USER_AUDIT_ACTION_BANNER = /Oracle/CRP/DEV/db/tech_st/11.1.0/appsutil/template/txkDBSecUserAuditActionBanner.txt


IFILE=/Oracle/CRP/DEV/db/tech_st/11.1.0/network/admin/DEV_testebs/sqlnet_ifile.ora


***********listener.ora******************

DEV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.14)(PORT = 1526))
)
)

SID_LIST_DEV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0)
(SID_NAME = DEV)
)
(SID_DESC=
(SID_NAME = NILGDB2)
(ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0/)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH = /Oracle/DBlink_RPM/ODBC_Driver/odbc_cli/clidriver/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
)
(SID_DESC=
(SID_NAME=BACKOFF)
(ORACLE_HOME=/Oracle/CRP/DEV/db/tech_st/11.1.0/)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/Oracle/DBlink_RPM/ODBC_Driver/sqlncli-11.0.1790.0/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
)
)


)


*************tnsnames.ora*********************

BACKOFF=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))
(CONNECT_DATA =
(SID = BACKOFF)
)
(HS=OK)
)

**************initBACKOFF.ora*******************

HS_FDS_CONNECT_INFO = dblink4sqlserver
HS_FDS_TRACE_LEVEL=255
HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_FDS_CONNECT_PROPERTIES='120'


set ODBCINI=/usr/local/etc/odbc.ini

*************************************************


Regards

Dharma

Dear mike,

Please refer these trace file also,


Exiting hgolofn, rc=0 at 2012/08/13-19:14:47
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 ".,"
Exiting hgoinit, rc=0 at 2012/08/13-19:14:47
hostmstr: 0: HOA After hoainit
hostmstr: 0: HOA Before hoalgon
Entered hgolgon at 2012/08/13-19:14:47
reco:0, name:sa, tflag:0
Entered hgosuec at 2012/08/13-19:14:47
Exiting hgosuec, rc=0 at 2012/08/13-19:14:47
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using sa as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2012/08/13-19:14:47
HS_FDS_CONNECT_INFO = "dblink4sqlserver"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2012/08/13-19:14:47
dsn:dblink4sqlserver, name:sa
optn:
##>Connect Parameters (len=40)<##
## DSN=dblink4sqlserver;
#! UID=sa;
#! PWD=*
Exiting hgogenconstr, rc=0 at 2012/08/13-19:14:47


Regards
Dharma

Hi Dharma,
In the gateway init file can you remove the parameter -

HS_FDS_CONNECT_PROPERTIES='120'

and change the parameter -

HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII

to

HS_NLS_NCHAR = UCS2

and try from a new SLQPLUS session.

Regards,
Mike

Dear Mike,

Now error is changed,

SQL> select *from "store"@ORACLE_SQL;
select *from "store"@ORACLE_SQL
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[



Regards
Dharma

Dharma,
It looks like we are nearly there and you are probably hitting the problem is this note -

Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql or SQL*Server (Doc ID 756186.1)

To check add the following to the gateway init file -

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

If this doesn't work then send the output from -

select * from nls_database_parameters ;

and a 255 gateway trace.

Regards,
Mike

Dear Mike,

As per your previous post,i have changed initBACKOFF.ora file parameter
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1.

Now error has been changed,previously i posted same error,


Please refer trace file below.

HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
Exiting hgoinit, rc=0 at 2012/08/14-00:00:52
hostmstr: 0: HOA After hoainit
hostmstr: 0: HOA Before hoalgon
Entered hgolgon at 2012/08/14-00:00:52
reco:0, name:sa, tflag:0
Entered hgosuec at 2012/08/14-00:00:52
Exiting hgosuec, rc=0 at 2012/08/14-00:00:52
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using sa as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
Entered hgocont at 2012/08/14-00:00:52
HS_FDS_CONNECT_INFO = "dblink4sqlserver"
RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
Entered hgogenconstr at 2012/08/14-00:00:52
dsn:dblink4sqlserver, name:sa
optn:
##>Connect Parameters (len=40)<##
## DSN=dblink4sqlserver;
#! UID=sa;
#! PWD=*
Exiting hgogenconstr, rc=0 at 2012/08/14-00:00:52


select * from nls_database_parameters ;


PARAMETER VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.1.0.7.0


Please Refer above


Regards
Dharma

Hai mike,

Waiting for your replay

Regards
Dharma

Dharma,
I'm checking the information you have sent and will get back.

Regards,
Mike

Dear mike,

Thanks for your replay

Regards

Dharma

Dear mike,

Once again,I willl post initBACKOFF.ora file.

HS_FDS_CONNECT_INFO = dblink4sqlserver
HS_FDS_TRACE_LEVEL=255
HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
#HS_FDS_CONNECT_PROPERTIES='120'
#HS_NLS_NCHAR = AMERICAN_AMERICA.WE8ISO8859P1
#HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
#HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
#HS_LANGUAGE = AMERICAN_AMERICA.AL16UTF16
#HS_NLS_NCHAR = AMERICAN_AMERICA.AL32UTF8
#HS_LANGUAGE=AMERICAN_AMERICA.EE8ISO8859P2
#HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
#HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
HS_LANGUAGE = UTF8
HS_NLS_NCHAR= AL16UTF16


HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
HS_NLS_LENGTH_SEMANTICS=CHAR


Regards
Dharma

Dear mike,

I am waiing for your replay,

Regards
Dharma

Dharma,
I'm still looking at this and will get back when I have an update.

Regards,
Mike

Hai Mike,

Thanks mike,i am also searching still i cant get good results

Regards
Dharma

Dear mike,

I refer this Metalink ID

Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql, SQL*Server or DB2 [ID 756186.1]

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

Solution:
Add the following to the DG4ODBC parameter file initSID.ora -

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1


But,my Oracle Database NLS_CHARACTERSET = UTF8

which parameter is the UTF8 is equal to HS_LANGUAGE ?


Regards
Dharma