Using a long Oracle-based query as either an Access passthrough or Microsoft Query (passed to Excel)
I have a very long SQL statement that was written for Oracle and kept in a 64KB (!!!) file. The SQL statement runs just fine in my Toad application, and I am able to create a connection to the schema in Access to run as a Passthrough query and also a connection in Microsoft Query to pass the data back to Excel. The sheer volume of SQL code, however, is too great for Microsoft Query and Access to handle. What would be very nice, and what both of these programs (seem to) lack, is the capacity to take as input a file name containing the SQL statement. I would love to be able to have Access and Excel point to a file containing the desired SQL statement and simply command it to run that file. The advantage of such a scenario would be that, on changing the SQL file from within Toad, I could first validate that the results are correct and then render those results automatically and dynamcially in Access and Excel. Achieving that seamless migration would be a real coup for me. Any ideas on how to go about this? Thanks, ~Peter Ferber
Why don't you encapsulate that SQL query into a simple view, then retrieve data in Excel via ODBC.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I can't speak to how you would do this with Excel but with Access, you can store whatever you want in a table and use that stored value however you want. So, you would create an interface to import the SQL file you created with Toad and store it in a memo field in a table. Be sure to add several columns so you can easily find this query again so you can run it.
To run the query, use DAO to open the table and locate the query you want to run. Copy the query into a string and then run it with DAO as a pass-through query.
To run the query, use DAO to open the table and locate the query you want to run. Copy the query into a string and then run it with DAO as a pass-through query.
ASKER
Of course, that's the answer! I'm accustomed to writing stored procedures and views in SQL Server, but I'm somewhat new to Oracle. Since your poste, I've put in a request to be granted privileges to create views and stored procedures. Thank you.