Toad World® Forums

Using Oracle SQL query language while connected to Exel spreadsheet

Hello,

Using TDA Professional 3.0.1.1734 in an Oracle environment

Connecting to an Excel 2007 worksheet

Questions:

  1. How can I use full Oracle SQL language to query the Excel spreadsheet, e.g., minus set operator doesn’ work…no to_date or to_char…and more…

  2. When I look at the functions available while trying to do a where clause in Build a Query, the functions look like Microsoft functions and not Oracle

  3. Trying to restrict rows returned in query based on a date column, not getting correct results

  4. The Excel date column comes in as VARCHAR(255) don’t know if this should be changed to a Date field, I am unable to change it.

My experience is limited to writing Oracle SQL queries…any help on how/if I need to connect to the Excel sheet differently or on how I can use all of the Oracle normal query language would be greatly appreciated.

Thank you,

Bob

When connected to Excel you have to use the syntax that Microsoft understands. In this case it would be VBA functions. Many of then are not very similiar to Oracle.

Since you have the Pro edition I would suggest save to local storage. (On the right click menu). Local Storage is a MySQL instance and supports most of Oracle’s syntax.

Debbie

Debbie,

thank you for your quick response…very helpful.

Bob

Hello Debbie,

Just a thought,

…a selling point of TDA is cross-platform connections, connections to dissimilar databases. Sure would make things easier if TDA allowed a user to use the same language syntax for the platform it is installed on, for example in my case Oracle, so a user wouldn’t have to know or learn different SQL syntax for the multitude of different databases one might connect to. Just like language, I speak and write english and it would be very time consuming for me to try and communicate and/or learn a different language.

It is kind of like someone saying "isn’t it great, you can talk to the Chinese and the Japanese, and the Germans and the Italians with this wonderful new product" But as it turns out, as long as you learn Chinese, Japanese, German and Italian you will be able to talk to them, but if you don’t, then you’re all speaking different languages…as the old saying goes, "that’s Greek to me"

So when connected to Excel, seems like TDA should translate the Oracle SQL commands to something Excel understands or better yet, when TDA connects and brings in the Excel file it is converted so Oracle SQL (my platform) syntax can be used on it. Time intensive and inefficient to have to learn the different syntax of the platforms one is connected to.

Thanks,

Bob

You gring up a good point and it has been discussed. If you use Cross-Connetion editors for your queries you would use the common mySQL syntax which does contain most Oracle syntax. This would be on way of standardizing syntax.

Debbie

Debbie,

again, thank you for your quick response and good ideas on solving a problem or ways to reduce the problem through work-arounds.

Appreciate the help,

Bob