Newbie problems using Excel and Oracle in same TDA query

Toad Data Point & Toad Intelligence Central
1

TDA is new in our office and it’s been great from querying from a single source, but I’ve had all sorts of performance problems trying to join a list in Excel with a table in our Oracle Banner database. I have boiled the query down to its most basic, joining the Excel identifier to one Oracle table to find its primary key. The query didn’t return any rows in 25 minutes, but I could run it in Access in ten seconds. We want to switch to Toad because it offers broader possibilities. It is actually working, however, because when I pare the data down to 10 rows (from 7000) I do get a result.

I am running W7, Toad 2.7.0.348, and Oracle 11. Having read other posts here I have tried using both the default and Oracle 11g ODBC connections. Both work when I am only pulling from a single source.

I have tried to edit the SQL in my query as well, but whenever I do I this error

ORA-01948: identifier’s name length (48) exceeds maximum(30)

because the table is specified as (this is the default connection version)

{{Odbc(“DRIVER={MicrosoftODBCforOracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORABAN.WCTC.EDU)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=WCTC)));UID=VBRENNER;PWD={0};,xxxxxxxxxxxxxxxxxx”)}}."SATURN.SPRIDEN"SPRIDEN

So I’m thinking there must be something we’re doing in how we specify our Oracle connection that is causing performance problems. And there must be some way that I can write SQL to select from a table that won’t violate the 30-character table name limit?

2

The cross-platform execution in TDA 2.7 is …well…not that great. We have compeltely redone this area for TDA 3.0. We will be including a brand new cross-platform exectuion engine in our Beta in a couple of weeks. Can you hold tight for that? And be a Beta tester?

If not, one work around might be to use Acces to link to Oracle. Then use Access to build a Query that joins Excel and Oracle. A saved Query in Access can be exectued in TDA.

Debbie

3

I think we would be willing to do that, if we would be able to have both Toad 2.7 and Toad 3.0 installed on the same machine? So that if we ran into a problem in the new version we could still do our work in the old…

4

Yes. They install side by side, no problem.

5

Thank you, I will try that. But what about the second part of my question? Is there some place I can assign an alias or synonym to a connection so that the SQL won’t make a table name out of the entire connection string, thus exceeding the Oracle 30-char limit? Or is that also something that is being addressed in Toad 3.0?

6

I took a closer look at your description of the problem. Cross-platform queries are executed using a different method and you would never get back an Oracle error. Is it possible that you are building the query in the Query Buildler but executing it in the editor using an Oracle connection? To execute Cross-platform queries you need to be connection to the Toad Sample database connection. Or only execute from the Query Builder.

Debbie