Creating/amending Oracle stored procedures via VBA

[snakehips2000]

I know there is the SQL-DMO object library which allows all objects on a SQL Server to be manipulated, i.e. databases, tables, views, stored procedures etc., but is there an equivalent reference within VBA for the objects in an Oracle database?

I'm particularly interested in changing the syntax of an Oracle SP on-the-fly via VBA and then executing it in much the same way as SQL-DMO allows it.

The basic reason for this question is that I need to dramatically improve performance of a series of Access queries which currently utilise tables linked to an Oracle back-end. It would make far more sense to use SPs on the back-end itself. However, the SPS themselves need to be amended on a regular basis and this could ideally be done from within the VBA application rather than on Oracle itself.

Thanks.

 

[PHV]

Why not simply use PassThru queries in Access ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

snakehips, you would just use the standard ORACLE SQL syntax to drop and recreate the stored procedures.

Establish a databash connection with your Oracle DB, build your CREATE PROCEDURE string, and send the query.
(This is the Passthrough query PHV is talking about)

I've done this in Excel, connecting to a Teradata DB, but not in Access.

Although, you really shouldn't have to create a stored procedure every time to accomplish this, you could just write the PL/SQL and send the query directly.

 

[snakehips2000]

Thanks for the replies PHV and Gruuuu.

I'm aiming to do this manipulation of the Oracle SP from Excel VBA. Essentially my project involves rationalising a large set of manual processes which involve three hierarchical levels: the Oracle back-end, an Access application (where the data is being manipulated via a series of stored queries) and Excel (where the results are presented).

My aim is to bypass Access completely so a standard Access pass-through query isn't the option. However, Gruuuu, I'm encouraged that you've achieved a successful outcome in connecting to Teradata and running a dynamically-created SP on it from Excel VBA. If you've got any code snippets on this I'd be most grateful.

 

[PHV]

Use an ADODB.Command object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Use an ADODB.Command object.

I went the [presumably] less versatile route of just adding an ODBC query table to Excel, and changing the CommandText before refreshing.

snakehips, this is fairly straightforward.
2003: Data->import external data->New Database Query
2007: Data->Get External Data->From Other Soruces->From Microsoft Query

You would select your oracle data connection from the list (installed with whatever Oracle Software allows you to query the Oracle DB), provide your credentials.

It should come up with the MS Query screen, which you can save a generic query into and click the "Return data to excel" button (or whatever it says)

Then, my code looks something like this:

Sub runqueries()

Dim strQry As String
Dim qt As QueryTable

Set qt = Sheets("QueryTable").QueryTables(1)

strQry = "SELECT fields FROM tables;"

qt.CommandText = strQry
qt.Refresh

fillquery is the function that builds my Query string. the Querytable is filled with the results of the query (which is only sent when you [tt]qt.Refresh[/tt])

PHV: Out of curiosity, what would be the advantage of using the ADODB object as you suggest?

 

[PHV]

what would be the advantage of using the ADODB object
1) not having all the overhead of ODBC
2) having all the native (Oracle for instance) SQL syntax at hand
3) using the Range.CopyFromRecordset method

Furthermore, most standard installation of ms-office lacks ms-query ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[snakehips2000]

Thanks to you both for your suggestions. I'm sure I'll find one which suits my needs.