ACC2: No Results When Calling ORACLE 7.0 Stored Procedure

• Microsoft Access version 2.0
• Microsoft Visual Basic version 3.0

SYMPTOMS

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

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, ...)}

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,...)

REFERENCES

For more information about Microsoft Access and ORACLE, search on "Oracle," and then "Network Configurations and ODBC Drivers" or "ODBC Drivers and Built-In Drivers" using the Microsoft Access Help menu.