Besides initDBLinkName and tnsnames.ora and listener.ora and ODBC configuration what do I need to configure in a windows server to set the DBLink on Toad

Toad for Oracle
1

Besides initDBLinkName and tnsnames.ora and listener.ora files and ODBC configuration what do I need to configure in a windows server to set the DBLink on Toad? The credentials are working on ODBC configuration

2

Good Morning Javierabo,

The user account, which requires to create the DB Link, needs to have CREATE DATABASE LINK privileges granted by the SYS user.

Cheers,
Norm. [TeamT]

3

Hi Norm thanks for the heads-up, the problem is with the tnsnames.ora file, I used to work on a similar project with Oracle 12c and windows server 2012, now I'm using Oracle 19c and windows server 2019, I already configure the tnsnames.ora, the listener.ora, the initDBLinkName.ora, and the ODBC where the credentials were succesfull, but in toad still show me the "ora-12154" error, I already have everything like in the old project but I'm having trouble with the new versions, really appreciate any help

4

I have never heard of any need to edit an initDBLinkName.ora or ODBC with DB Links. And If you make the DB Link so that it includes the TNS Info, then you don't need to do anything with TNSNames.ora. Like this:

CREATE DATABASE LINK db_link_name
 CONNECT TO user
 IDENTIFIED BY password
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=name_of_server)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=name_of_database)))';

If you make it like that, then the only thing other thing you need to make sure is that the database where the DB Link lives has network access to the target database.

edit: Oops I did "reply to Norm", I meant "reply to Javierabo"

5

What John said!

I've never heard of that file either and when I Googled for it, the only two hits were on this thread!

Where I disagree with John is in using the full tnsnames configuration in the creation of the link. It has caused problems in the past where a production database was cloned to test/QA etc, and the database links were all still pointing at production. Not good when testers are testing and updating production! Even when there are SOPs defining how to clone databases between environments, some "fat fingered DBAs" think they don't need to follow procedures. (No, it wasn't me - I had to fix it!)

For best results, use a generic name for the link, and a tnsnames alias for the remote database. In each of the dev, qa, sit, uat and production tnsnames files, the same alias point to the appropriate database for that environment. No changes required when cloning between envionments.

Getting back to the original problem, I'm not sure why a link is required when creating an ODBC connection. As far as I recall, you don't need a link, only the tnsnames alias to create the ODBC thingy. (Technical term!)

What exactly are you attempting to do please?

Cheers,
Norm. [TeamT]

6

I find using that info in the DBLink definition handy for a short-term DB Link when I don't want to visit the server to configure TNSNames.

But I agree with you Norm, for production work (or if TNSNames is already set up on the server) then it's better to use tnsnames.ora.

7

If you are going Oracle to Oracle, all you need is a TNSNAMES entry that points to the remote database.

If you are using Heterogeneous Services to connect to SQL Server, Javierabo is correct. You need

  1. ODBC entry to point to SQL Server
  2. Tnsnames entry that references SID of ODBC
  3. InitXXXX.ora file in hs\admin folder where XXXX is the sid of the ODBC connection, and inside the file it references the SID of the SQL Server
  4. Entry in the listener.ora file that references program DG4odbc and SQL Server SID

I use a lot of these (25 on one server). They are very quirky to set up. I have found at times that I have to reboot the server (restarting DB doesn't do it) to have a new entry recognized. Once set up, they have been rock solid. We have critical systems exchanging data between Oracle and SQL Server using this method. Heterogeneous Services are included in the basic licensing, there is no additional charge for using it.

It makes life really simple to be able to execute statements against SQL Server (select * from dual@hssql127.world). One thing to remember , Oracle by default translates everything to upper case, SQL Server does not. That means that multi-case table names and column names must be enclosed in double quotes. I have found it simpler to create a view on SQL Server where the view name and the column names are all in upper case. Then I don't have to be concerned with case.

ODBC Connection
HSSQL077

TNS Entry:
HSSQL077.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
(CONNECT_DATA =
(SID = HSSQL077)
)
(HS = OK)
)
Listener.ora Entry
(SID_DESC =
(GLOBAL_NAME = HSSQL077.world)
(ORACLE_HOME = d:\Oracle\19)
(PROGRAM = dg4odbc)
(SID_NAME = HSSQL077)
)

initHSSQL077.ora Entry:
HS_FDS_CONNECT_INFO = HSSQL077

1 Like
8

Hi Brian,

Thanks a lot for your answer, is not SQL Server but Informix but I think it's the same (to a non Oracle DB), I already have exactly what you mentioned and shared, actually I used to work on a similar project (Oracle 12 and Windows Server 2012), this is kind of an upgrade (Oracle 19 and Windows Server 2019) and my concern is if there´s some restrictions with this new versions

Not sure what could be wrong

ODBC.ini
CMS70

TNSnames:
CMS70 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
(CONNECT_DATA =
(SID = CMS70)
)
(HS = OK)
)

Listener.ora
(SID_DESC =
(ORACLE_HOME = D:\app\oracle\db_home)
(SID_NAME = CMS70)
(PROGRAM = dg4odbc)
)

initCMS70.ora
HS_FDS_CONNECT_INFO = CMS70

9

...
Not sure what could be wrong

ODBC.ini
CMS70

I assume this specifies the tnsnames alias to be used when connecting?

TNSnames:
CMS70 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
(CONNECT_DATA =
(SID = CMS70)
)
(HS = OK)
)

Maybe it's the formatting in my (received) email, but:

In tnsnames.ora, you never have the opening brackets in column 1. There
is/was/might still be a bug in that it would not work (or work
correctly) as an alias in this case. I am not sure it it still is a bug,
so best avoided to be sure.

I wonder if "SID = " has finally been deprecated? It was supposed to be
being removed since 9i. Perhaps it will work if you change "SID =" to
"SERVICE_NAME ="? Just a thought. The default service name is the same
as the old SID, so that should be all that's required.

Other than that, I see nothing amiss in the tnsnames entry.

Listener.ora
(SID_DESC =
(ORACLE_HOME = D:\app\oracle\db_home)
(SID_NAME = CMS70)
(PROGRAM = dg4odbc)
)

initCMS70.ora
HS_FDS_CONNECT_INFO = CMS70

According to the docs, this entry should be
"HS_FDS_CONNECT_INFO=host_name:port_number/server_name/database_name"

Where:

Host_name is the host name or IP address of the machine hosting the
Informix database.

Port_number is the port number of the Informix database server.

Server_name specify the Informix database server name.

Database_name is the Informix database name.

Plus, the filename for this should be:

$ORACLE_HOME/GATEWAY_SID_NAME/admin/initGATEWAY_SID_NAME.ora

$ORACLE_HOME is where the Oracle Gateway software is installed.
GATEWAY_SID_NAME is the, ahem, gateway SID name.

The Oracle docs for a connection to Informix is at
https://docs.oracle.com/cd/B28359_01/gateways.111/b31042/installinformix.htm
if required.

10

Hi Brian, Thanks a lot for your quick response.

Let me review what you sent, I already test with the service_name but let me try again, at the moment I'm getting this error:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 2 lines preceding CMS70

11

Hi Norm,

Thanks for replying, maybe something is missing on the information sent, already has been set like all of you kindly advice, but I think what's the issue. Will reply all with another question

12

Hi All,

Thanks a lot for your follow up, I'm working with two non Oracle databases, Informix and Teradata, in the last project I used to work with both DBLinks, now I'm trying to configure the same in the new environment, reviewing all the items to configure, I noticed that the Informix Driver is 32bits but the Teradata driver is 32/64bits, when I tried with the Teradata DBLink it works, so now my question is where to find the IBM Informix ODBC Connector for Windows 64-bit, not in https://www.progress.com/odbc/ibm-informix and not in https://www.ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html, if possible from other source because none of this works, thanks in advance.

13

javierabo : I have been on vacation the last week, hence the slow response.

When I have received ORA-28545 it is because I fat-fingered (mad an error) in the setup. Carefully go back over each step and make certain the entries are correct with the correct naming. Most recently, as in three weeks ago, I received that error because I neglected to add the appropriate TNSNAMES.ora entry.

14

Hi Brian,

Let me review it again, I'm no 100% sure about the 64 driver, you use that? I'm getting ORA-28500: ORACLE connection to non-Oracle system returned this message: [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. {42000,NativeErr = -201} but seem to read the Informix table, not sure what's wrong now because if it reads the final table means that the configuration files are ok right?

15

@javierabo We don't use Informix, so I can't help you out there. We do use the 64 bit SQL Server ODBC drivers. You said "it reads the final table." Are you trying to run two SQL commands at once? That works in SQL Server, it does not work in Oracle. Oracle does not handle the return of multiple cursors. Each SQL command must be a separate call.

Don't include a trailing semicolon (';') on the SQL statement.

Test with the easiest of all statements: "select * from dual@dblinkdb". I know that works against SQL Server, I have used it numerous times.

Use Oracle syntax, not Informix. Again, using an example from SQL Server, "select abc from db..dbo.table" is perfectly valid SQL Server syntax but invalid Oracle syntax. It is Oracle that is doing the translation, so you have to conform to its rules.