Toad World® Forums

Cannot join an Excel Spreadsheet to Oracle Tables


#1

I am using TDP v3.6.1.3294

I cannot create a cross-connection query between an Exel file and an Oracle table. I get the ‘Cross Query Database Error’ ‘Invalid data source definition: Data source name not found and no default driver specified’

Here is what’s odd. Query Builder is creating the query.

Here are my steps.

  1. I open both data sources: Excel and Oracle.
  2. I drag the spreadsheet and the Oracle table into Query Builder.
  3. I select my fields and join the tables.
  4. When I run the query I get the error.
    Why is Query Builder creating an invalid query?

Respectfully,

Leon


#2

This is a super-simple query. In essence I am joining two table using a shared key. I don’t even have a where clause.


#3

Can you first try connecting to local storage and see if you have any issues. This is the internal database that processes that type of query.

Then post the query so I can see the query. What are the datatypes of the join columns?


#4

Thanks Debbie.

I connected to local storage first, but that did not make any difference.

Here is my query:

SELECT leh.VENDOR_NAME

, aps.SEGMENT1 AS Supplier#

, aps.VENDOR_ID

, aps.FEDERAL_REPORTABLE_FLAG

, aps.PARTY_ID

, aps.END_DATE_ACTIVE

FROM

Vendor-List.AutoRange_VendorList leh

INNER JOIN

PEBS (HAMPTONL), HAMPTONL.AP.AP_SUPPLIERS aps

ON (leh.SUPPLIER# = aps.SEGMENT1)

This query is my attempt to understand how to create cross-connection queries. I queried the AP_SUPPLIERS table and downloaded the vendors and their vendor number (aka segment1) into a spreadsheet. Column A is the vendor name; column B is the vendor number. Then I deleted all rows but thirty.

Now I’m trying to see whether I can join that spreadsheet to the AP_SUPPLIER table and list only those vendors in the spreadsheet.

Respectfully,

Leon


#5

Do you have Oracle instant client or full client? If you have instant client, you will need to download the Oracle ODBC driver before performing a cross connection query. If you have full client, the ODBC driver gets installed with it.


#6

I have the full client.

When I use Query Builder to build a query of just the spreadsheet the query runs. Here is that query.

SELECT AutoRange_VendorList.VENDOR_NAME

, AutoRange_VendorList.SUPPLIER#

FROM AutoRange_VendorList AutoRange_VendorList

Respectfully,

Leon


#7

Send the query to a Cross-Connectio Edtior. Add these key words to the front of the query. Highlight the whole query and execute. It will return one row of info. Copy and paste that here for us to see.

/LOG_XQUERY/ EXPLAIN