Launch an Oracle stored procedure from VB

10-12-2004, 07:24 AM
Is there any way to launch an oracle PL/SQL stored procedure ????
I can manage (create and open) the oracle Db connection...but then?????
Have you any ideas/tips/suggestion????

Thanks in advance

10-12-2004, 08:29 AM
You are using ADO...right??? Im not all that familiar with the workings of the Oracle provider, but ADO is ADO... this should do you.

Dim llHowManyRecordsWereAffected As Long 'This is really optional

'loConn would be your ADO connection object, already connected, etc
loConn.Execute "procStoredProcedure", llHowManyRecordsWereAffected, adCmdStoredProc

10-12-2004, 12:57 PM
Or if you're using Oracle Objects for OLE instead:
Dim llHowManyRecordsWereAffected As Long 'This is really optional

'loDB would be your OraDatabase object, already connected, etc
llHowManyRecordsWereAffected = loDB.ExecuteSQL "procStoredProcedure"

10-13-2004, 01:58 AM
thank you both, this is very useful.

10-13-2004, 02:52 AM
Just a tip. I created two similar application, one using ADO and the other is OO4O (Oracle Objects for OLE). The results were astonishing. I was expecting ADO to be inferior with OO4O, but it turned out opposite.
This difference can be clearly noticed when dealing with loops.
ADO is 4X faster than OO4O. (This is as far as I noticed...) :)

10-13-2004, 03:56 AM
Yes, I've noticed the same thing with DAO connection, which is slower than ADO. ADO is much better and fastest.

10-13-2004, 08:39 AM
Interesting observation, since in the main large-scale application where I use OO4O, I switched to it because its performance significantly outstripped ADO. I suppose it depends on specifically what you're doing with it.

10-13-2004, 09:46 AM
and, since ADO had more overhead than DAO (one abstraction layer more) it is reported to be a bit slower than DAO. I suppose, as with most things programming, how you apply the technology is often of greater importance than the technology itself.

10-15-2004, 06:44 PM
Hmmm..have to agree, I have never experienced ADO being faster than OO4O. Actually, I have heard comparisons such as:

"The OO4O works 5 times faster than DAO and 8 times faster than ADO."

While I also think this is extreme (and I have never seen THAT big of difference),unlike other COM-based database connectivity APIs, such as ADO, the OO4O Automation Server was developed specifically to work with Oracle. It provides optimized API for accessing features that are unique to Oracle. So many of the cumbersome performance issues that come from using ODBC or OLE database-specific components are handled much better.

I guess beauty is in the eyes of the beholder :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum