I could really use some help with this cross query connection stuff. I am about at my wit’s end with it. It seems like it should be simple enough (at least by way of all the information and tutorials I can seem to find), but it is an absolute nightmare! The best I can get, in terms of incessant errors, is the following:
“An Oracle Client and ODBC driver are required for cross-connection queries. Toad is unable to find the OBDC driver. Please install missing client files, or choose the driver/Oracle home you want to use from the Advanced tab in the Connection Properties window.”
I am using TDP 4.3.0.718. This error pops up any time I create a cross-connection query builder and populate it with any data from my Oracle connection (I do not actually need to have any other data sources present). I am not a DBA or some programming wizard, just a lowly finance user trying to make his way in the world… I installed Oracle Client… I installed ODBC driver… I tried EVERY SINGLE “driver/Oracle home” from the Advanced tab… please… help… I’m beginning to think that this cross-connection thing is just some evil joke, and I’m clearly the punchline.
First of all Wes I would like to thank you for one of the funniest TW posts ever. You had me laughing this morning.
Now let's see if we can get you sorted out.
If you are only gathering data from Oracle then you do not need to use the cross-connection query builder. Just try using the 'standard' query builder. The cross-connection feature is used when you are trying to gather data from Oracle and another source, say a spreadsheet, or DB2, or Mongodb, etc.
If you are gathering data from multiple sources then the cross-connection query builder is the way to go. For your Oracle connection the first step is to make sure you can connect to Oracle via the client and the proper Oracle Home:
Once you can connect to Oracle you can then right-click on that connect link in the Navigation Manager and set the ODBC connector to point to the same Oracle Home:
From here you can expand the connections and see the objects available to you and then drag them into the cross-connection query builder and start creating your queries.
Let me know if this helps or if you have more questions.
Thank you for your thorough and prompt reply! I did not have an exact replication of the “current home,” but the choices that I did have basically self-narrowed enough to quickly discern the correct one (“Oracle in instantclient_11_2”). The funny thing here, perhaps, is that all of these options were here yesterday as I tried them one by frustrating one, and even this one did not work. However, that was before I resorted to downloading and installing this same client (Oracle 11.2), after which I did not try changing the OBDC driver setting again (simply left it on ). Today, changing it to that noted above allowed my first successful use of cross-connection.
I even added an Excel source and joined the tables… still no errors! I am now hopeful! Your timely intervention talked me down from the ledge just in the nick of time! Now my new concern is that my employees didn’t look as relieved about that as I would have expected…
Follow up concern… Use of the cross-connection is untenable. After struggling with it all day, I can only conclude that, functionally, TDP seems to have a similar issue to early 3.x versions when using subqueries. I cannot confirm that this is sub-query related only, but each action I try to take on the query (using query builder), such as recoding the ‘where’ clause adding “group by” to a field, etc., results in delays as long as fifteen minutes before the program will allow further interaction.
Is this a known issue? Should I be avoiding the use of sub-querying when using cross-connection?
Thanks!
EDIT: Further struggling with this query into the evening confirms to me that delays are within the subqueries only (all data sources, though larger tables result in substantially longer delays, as if the system is flushing through the underlying data each and every time I modify the query). Within the main query, it functions just fine.
Hi,
you have great solution. I am new to Toad Data Point.... just know enough to set up my ODBC using Oracle driver since we have oracle database. I was able to see the feature on your screenshot but TNSname and SQL editor both are grayed out.... but I was still able to set it up for one of the connections and able to access oracle database at the beginning.....
not sure what happened now when I right click on the oracle connection and properties I don't see the dialog box any more and even if I try to set up a new connection and select oracle for the group I don't see the middle section of login or advance tab. Can you help on that?
First of all, why are you right-clicking on the connection? Does the connection not work anymore? That would be the only reason I could see for doing the right-click. That being said, what has changed in your environment? Did the dba's change databases on you (did they move or drop your database)?
What happens if you double-click on the original connection?
Hi Mark,
Thank you so much for the quick response.
I was clicking on the existing connection to see how I set it up since I have multiple database to set up. I know enough to be dangerous so I am trying to see what I did to set the first one up
That is when I noticed I can no longer bring the property dialog box up…. I was able to before.
Since I can’t see the property dialog box of the existing oracle connection , I tried to create a new connection… however, when I click on Oracle once from the dropdown it doesn’t take me to the next screen where I should see login and advance tab with TNSname editor and SQL editor….here is what I see but if I click once on other options from the list it takes me to the next screen to enter connection info
sorry I can only do 3 replies on the same topic so hopefully you can see me updated this post.
yes I was trying to install Oracle in OraDB12Home1(64 bit) but I think it was wrong driver and was installed so I ignored it and just used the old one I had Oracle in OraDB11Home1(64 bit), however, I installed first before I configured my first connection so not sure what cause it to all of the sudden not available. I check control panel and no new oracle driver was installed even for me to uninstall, looks like Path for Oracle is good and first Oracle connection is still working fine but I just could not get property dialog box of existing one open to see how I set it up or even new connection is not showing me all the log in or advance tabs once I select Oracle
This is very odd behavior. Can you open up a support ticket? Also, I have added some new code to this area that i might want to have you try. We have an existing issue where you will not get a display if you have a registry setting to a bad Oracle client. Have you done any installs/uninstalls of Oracle clients since you started seeing this error?
