Toad World® Forums

Configuration Help for Cross-Connection Query with Oracle


#1

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.


#2

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.

  1. 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.

  2. 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:

SAVE this update.

Now you should be able to connect using the cross-connection Query Builder:

Your Object Explorer should now show any data sources you have connected to. In the example below I have an Oracle connection and an Excel connection:

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.

-Mark


#3

Mark:

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… :wink:


#4

LOL. I am glad I could help and don’t hesitate to post any future issues you, and your employees, may have. We want you in your seat, not on a ledge. :slight_smile:


#5

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.