Executing complex Oracle queries in Excel using PL/SQL

[Mark Olsen]

Hi,

Im basically trying to get oracle data into excel by using an ADO ODBC
connection. I was wondering if I am able to build complex queries in PL/SQL
that can include functions and procedures. I use TOAD(A database interface)
to build by queries then I read them into excel to execute them. TOAD wont
let me put my actual SQL query and PL/SQL functions and procedures in the
same file. Is this just something with TOAD or is it impossible to have one
text file that contains the query and all necessary functions? Or is it even
possible to write your own functions and use them on a remote read only
database? I know I can run a few queries, dump them in excel and then
manipulate them with VBA functions to get what I want but I was wondering if
I could just do everything in the query and have it just do a data dump into
a spreadsheet.

I hope this makes sense to everyone. Sorry if it doesnt. I guess it boils
down to: Can I use the full functionality of PL/SQL through excel?

Mark

 

[tony h]

Using pass-through queries you can execute the oracle sql directlr o
the Oracle server.

I can't test it at the moment but from memory you cannot format a P
SQl and expect it to execute but if it is a stored procedure on th
Oracle database you can execute it using a pass-through.

My usual plan is to model and test using access (including th
pass-through elements) as this just operates as a front end to dat
objects and then code it in Excel.

Hope this helps