Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33

    Unanswered: Using Excel (MS Query) with Oracle

    I created a custom query using Microsoft Query to query a schema on an Oracle 10g database. The query works fine in SQLPlusW and in MS Query. But when I try to run the query from Excel (Data->Get External Data->Run Saved Query), I get an error from Excel that "MS Excel could not open or read this Query file. Either the file has been damaged or the file format is not valid." Anybody got a clue? Like I said, if I open the .DQY file using MS Query, it executes just fine. It is a bit complex query using right outer joins and dense_rank() etc., but I am extremely doubtful if Excel cared about that since it only needs data returned from MS Query. Any help is appreciated.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I have had similar funnies with complex queries in Excel. Mine I couldnt even get into MSQuery, but I could run it through PL/SQL developer. Try creating the query as a view on the database and use a simpler sql select from Excel. That worked for me...

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Thanks for your reply. The problem is that the query is dynamic and that would mean I will have to recreate the view everytime the query changes (not just the where part, but the no. of columns selected as well). And I don't want to give any other access other than SELECT to the users using Excel or other tools. They can select from SOME table(s), but not create or replace tables views etc.

    But yes if I create it as a view, I am able to select from Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •