Toad World® Forums

Viewing Oracle tables in cross connection query


#1

I want to create a cross connection query. The query will mainly source from Oracle and some of it will source from SQL Server. I connected to both systems and clicked to open a cross connection editor, but then the Object Explorer will only display the tables from the SQL Server connection…nothing happens to the connection tree view when I click/double-click on the Oracle connection. I have tried changing the explorer to use a different display mode than connection tree, but it will not change to drop down, etc.

Another strange thing is that if I right-click the Oracle connection and select View Details…it does display the details of the first table in that connection…but I can’t see that table or any others from Oracle in the Explorer.

Version 4.3.0.718 (32 bit)


#2

can you use the Cross Connection Query Builder instead and see if that works for you? This should bring in the tables from different sources and let you create the connections between them.

The Cross Connection Editor bring in a sql statement for each individual object that you select which is probably not what you want to see.

cross-connection-editor.png


#3

In my previous reply i showed how the cross-connection editor will generate individual statements for you from multiple sources. In this example I am using the same tables in the cross connection query builder and building a query that joins various fields to extract the data.

tdp-query-builder-multi-output.jpg

-Mark


#4

If I start with a Cross Connection Query Builder, then I can’t browse the tables in the Explorer for Oracle (works with SQL Server). This may be due to an issue with Oracle, but I would consider it a bug.

If I start with a regular Query Builder, then I can browse and drag in any table from any connection source. Once I drag in a table that is from a source different than the first, then Toad presents the Cross Connection Query banner on the right side of the window.

I had not used the feature in quite some time and starting the way that I happened to start confused me.


#5

hmmm, that is weird. I wonder if is is a permission issue on your end or something.

This is what I see when I have 2 or more connections:

tdp-cross-connection-browser1.png

Above I see all of my connection for the cross connection query builder.

tdp-cross-connection-browser2.png

These are from my Oracle connection (above) and below is a general view of my Oracle and SQL sources

tdp-cross-connection-browser3.png

What type of Toad Data Point license are you using? Base or Pro?


#6

When you use Cross-Connection you should have an Object Explorer that shows all open connections and allow you to drag and drop objects into the editor/Query Builder, etc. if you do not see this just set the Object Explorer View to Connection Tree list.

connectiontreelist.png


#7

Yes, it appears just like that, but the Oracle connection won’t expand to see any other objects…the SQL Server does expand and I can see databases and tables within each database.


#8

Mnnn…not sure what would cause that.

Somethings to check before taking it to support would be:

  1. Are you using an Oracle OCI connection? (Instead of direct connect)

  2. Does the bitness of your Oracle OCI client match the bitness of Toad?

  3. Do the Oracle objects list when you only have one Oracle connection?

  4. Go to AppDataDir in Help|About. Close Toad. Rename AppDatDir. Restart Toad. Does this correct it? (This gets rid of any cached files that could be causing the issue.

  5. Got to AppDataDir in Help|About and look at ExcectionTrace.log. Are there any suspicious error stacks there that might relate to this?