TDM and ORACLE_HOME

Is there a way to directly tell TDM where the ORACLE_HOME is? I’m using the InstantClient install and there’s no registry entries (that I can find) for TDM to use, and it’s not using the ORACLE_HOME environment variable.

I got Toad for Oracle to work by copying my tnsname.ora into it’s root dir, but TDM needs to find the DLLs from ORACLE_HOME, so that’s not enough.

Hello Sean,

I’ve forwarded your question to our developers, they will check it out.

For now, you may try the connection via TCP/IP - in this case you don’t need any client or instant client.

As soon as I get some information from my colleagues, I’ll get back to you.

Thanks for your patience.

If you have any questions, please do not hesitate to write us back.

Regards,

Vladka + TDM Team

Hi Sean,

If you would like to connect through the InstantClient to the Oracle Server you don't need to specify the ORACLE_HOME. Just let the "Home:" field empty, because ORACLE_HOME is not relevant when you are using InstantClient. But don't forget to add into the environment variable PATH (My Computer->Properties->Advanced->Environment Variables) the directory where your instant client is located otherwise TDM will not find the OCI.DLL. To specify the database you want to connect to use either the

  1. tnsnames.ora file

In this case select from the available service names the service name you want to use. Again don't forget to specify the environment variable TNS_ADMIN. Set the TNS_ADMIN environment variable to the path where the tnsnames.ora file is located, but NOT including the tnsnames.ora filename itself.
*E.g.: TNS_ADMIN=C:\MyInstantClient
*
or

  1. the EZConnect syntax

If you rather than using the tnsnames.ora file specify all the information in the connect string to build the connection use the EZConnect syntax.

The format is: **//host:port/service_name

**Write this connection string into the field "Net Service Name". If you get a complaint about accessing NLS data files, you will have to set up the NLS_LANG environment variable.

*E.g.: NLS_LANG=AMERICAN_AMERICA.UTF8

David

Thanks for the help. but still no joy. I already had ORACLE_HOME and TNS_ADMIN set as system environment variables, and I already had my instantclient dir in the PATH as suggested, for sqlplus. I tried adding the path in the Home field which led to this error:

"Cannot find OCI DLL: in C:\Oracle\instantclient10_1"

I verified that the dll is really there:

Directory of C:\Oracle\instantclient10_1
03/07/2005 08:35 PM 188,416 oci.dll

and that it has read permissions for all. I also tried every combination of forward and backslash, with and without drive name, with and without trailing slash, etc. This one has me stumped.

BTW - this is a licensed copy that my company bought - version 3.0.11.11. Maybe I need an update?

OK - I copied the oci.dll into the TDM dir and it was used. I was then able to use the connect string method to make the connection. I’d still like to get the tnsnames working because this is a RAC install and it would be good if it had all of the node names.

But it works - did an RE import and my ugly DB schema is now a slightly less ugly diagram.

Hi Sean,

You definitely need an update. You’re still using the first TDM version and since the first release TDM has been improved A LOT.

Please download the latest version 3.2.4:
http://www.quest.com/common/registration.aspx?RequestDefID=10037

or via the https://support.quest.com Toad Data Modeler, Downloads and Updates section.

In case of any problems, please do not hesitate to write us back!

Thanks.

Regards,

Vladka + TDM Team

UPdate: I installed TDM 3.2.4 today, and the problem is exactly the same. Oracle_home/tns_admin environment vars aren’t read, tnsnames isn’t found, providing a direct path to Oracle Home (using native client connect) is ignored, and the oci.dll isn’t found unless I copy it into the TDM dir.

I also confirmed another issue I ran into when trying a plain TCP connect:
Connection failed because of the following error:“NET: Invalid SID”

As I’m sure you know using SID has been depecated and SERVICE_NAME should be used instead, at least for 10g and higher. In my case SID is simply not supported on the 10g RAC.

Again, I do have a workaround, but it’s disappointing that it would be this difficult to get TDM to follow a simple path to the Oracle installation. This is on a newer, plain vanilla XP system, BTW.

If you’d like more info just let me know.

Hi Sean,

Please make sure that the box Home is empty (without any content). If there is any text, feel free to delete it.

In any case, we will make a movie for you. Tomorrow it’ll be ready.

Thanks for your patience.

Regards,

Vladka + TDM Team

A movie? I appreciate the effort but are you sure you understand the question? I’m just trying to figure out how to get TDM to find the existing Oracle InstantClient installation and configuration. Is there a config box, or file, or registry setting, that I need to have? None of the suggestions above have worked.

I have another developer here with the exact same situation, BTW.

Sorry if I’m being a bother,
Sean

Hi Sean,

there is something about it http://forums.oracle.com/forums/thread.jspa?threadID=498185&tstart=0 .It’s about another product of Quest,but same solution.

Forum about Instant client

http://forums.oracle.com/forums/forum.jspa?forumID=190

First solution (make new env and copy tnsnames.ora) running fine for us with Beta and Full.

Tomas
QA

Hi Sean,

We think we understand.
I think the movie will further clarify it and will show you what we mean - what is necessary to do so as TDM would find OCI.dll etc.

A quick summary:

  • If you select TCP/IP connection, NO client or instant client is needed.
  • When you want to use instant client, you can select from using the tnsnames.ora file or the EZConnect syntax. In both cases, it is necessary:
  • to specify the environment variable.
  • restart TDM after you define the variable.
  • in the RE Wizard, step Connecting, delete the content of the Home box. And if you use tnsnames.ora file, you need to manually type the Net Service Name to the appropriate box (there is no option to select in this box, you need to type the name).

Please watch the movie that shows what to do. We hope it will help you.
http://modeling.inside.quest.com/entry.jspa?externalID=2072&categoryID=158

Sean, please write us your feedback. Thanks.

Note: If it doesn’t help, please write us version of the instant client you use, what Oracle version you are trying to connect. Thanks.

Regards,

Vladka + TDM Team
Message was edited by: vladka - added note

OK - first off thanks for creating the movie, I’m sure it will be helpful for other users.

However it also shows that you didn’t understand me when I said that I have tried all of these points. I do have TNS_ADMIN set (seriously), and I do have an tnsnames.ora. In fact both are used daily when I use SQLPLus. I am able to use the the EZconnect method, but that is only a workaround because I cannot specify all of the nodes that are listed in my tnsnames.ora file. The main problem here is that TDM doesn’t use the tnsnames file, either because it’s not found or not understood.

I’m going to drop this thread now since we’re just going in circles. If I get it figured out I’ll let you know.

Database = 10.2.0.3 RAC
InstantClient = 10.1
TDM = 3.2.4.3