Microsoft KB Archive/120491

= ACC2: No Results When Calling ORACLE 7.0 Stored Procedure =

Article ID: 120491

Article Last Modified on 6/28/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q120491



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
Although your Microsoft Access SQL pass-through query calls a valid ORACLE version 7.0 stored procedure, no results are returned.



CAUSE
This behavior occurs because the current ORACLE ODBC driver uses ODBC specification 1.0, and not 2.0. ODBC specification 2.0 is required to support IN-OUT parameters and the ability to return values in an interoperable fashion.



RESOLUTION
If the stored procedure that you are calling requires only IN parameters, you should be able to get results back, since the ODBC 1.0 specification supports this functionality. Stored procedures requiring OUT or IN-OUT parameters cannot be called from Microsoft Access or Microsoft Visual Basic.



MORE INFORMATION
ORACLE version 7.0 stored procedures are PL/SQL blocks (that is, they are not queries). PL/SQL procedures are similar to PASCAL procedures. They, like SQL Server procedures, can accept IN, OUT, or IN-OUT parameters, and can contain functions that return values.

The only procedures that you can call in ORACLE version 7.0 from Microsoft Access are those that require IN parameters. To do this, construct the statement as   {call StoredProcXX(Arg1, ...)} by concatenating the name of the procedure and its arguments.

There is no concept of a stored function in the ORACLE ODBC specification; therefore, you cannot call stored functions. The same is true for ORACLE packages which are ADA-like constructs (data and methods wrapped in a package). To invoke a procedure in a package in ORACLE version 7.0, preface the procedure or function with the package name. For example:   PackageAA.ProcedureXX(Arg1, Arg2,...) This is not possible with the current ORACLE ODBC driver.

