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.