This topic came up and I found this Oracle documents about using ODBC. Here is an excerpt:
XIV. Calling a Stored
Procedure Via ODBC
[See also the Stored Procedures from Microsoft Visual
Basic section of this paper.]
The following is an example of the Visual Basic syntax for
calling a stored procedure via ODBC:
db.ExecuteSQL(“{CALL
procedurename(param1,param2,param3)}”)
NOTE: This
assumes input parameters only and that you have assembled this such that each
of the parameters is embedded into the string as a literal. Also note that this
syntax DOES NOT work with packaged procedures, for those you must use the
alternative begin …end; syntax
In the above example dB is assumed to be a valid database
object. If you are using a tool such as MSQuery just use the {CALL …} (ODBC
Procedure Call Escape) syntax without the double quotes. You must include the
() even when you don’t have any parameters. Out parameters are supported at the
ODBC Level 2 conformance (Oracle7 ODBC Version 2.x). The Oracle Level 1 drivers
(Version 1.x) will not support this, you must be using a Level 2 or better
driver. The 7.3 Oracle driver does not support returning dynasets. This
functionality is first implemented in the 8.0.5.x version of the driver. An
alternative to the call syntax is shown below:
db.ExecuteSQL(“BEGIN
procedurename(param1,param2,param3); END;”, SQLPASSTHROUGH)
This alternative does require the use of the SQLPASSTHROUGH
parameter, but will also allow for calling packaged procedures (i.e.
packagename.procedurename()).
To return a result set with a stored procedure, refer to the
following Microsoft knowledge base articles:
- Q147938 (RDO)
- Q126992 (DAO)
The
Microsoft provided Oracle ODBC supports this functionality through the use of
PL/SQL table types. The Oracle provided drivers do not support this
functionality prior to version 8.0.5.x (where it is implemented in PL/SQL by
returning a REF CURSOR).
For simple output parameters from a stored procedure you
could use the following SQL:
{call procname(?,?)}
The above would be passed to SQLExecute() and
then have called SQLBindCol() or SQLBindParameter() for the output bind
variables (the variables referred to by the ‘?’) you defined in your program.
[Note: the Begin; … End; syntax would also work just as well here.] If you are
using the Oracle 8.0.x ODBC driver and receiving an ORA-6502 and/or ORA-6512
errors, you must upgrade the driver to version 8.0.3.0.1 or later and update
your MDAC to the latest version.