Calling stored procedures from Visual Basic (or VBA or VBScript) is preferable to embedding SQL in directly into the Visual Basic Code. In addition, being able to process the output from a stored procedure as an Active Data Objects (ADO) recordset adds considerable flexibility. The main aim of this example is to show a means of handling the stored procedure call, including a mix of input and output parameters of various types.

This example shows a Visual Basic program calling a stored procedure contained within an Oracle PL/SQL package. The VB code could equally well be embedded as VBScript in an Active Server Page in order to access the stored procedure from a web application. The VB code uses the Microsoft OLE_DB provider for Oracle to connect to the database. This component is included in the Microsoft Data Access Components (MDAC) distribution which may be downloaded from the Microsoft Web Site.

The example was created and run with VB V6.0 and Oracle V8.1.5.

To see an example using java and jdbc click here

Cut and paste the text from each of the text areas below. An easy way to do this is to click in the text area, use Ctrl-a to highlight all the text, then Ctrl-c to copy it to the clipboard.

First .. create some test data in the database

Next ... create the PL/SQL package
The GetEmployees procedure accepts an employee id and/or a range of hire dates as input parameters. The procedure locates matching employee records using any combination of the input parameters. Any parameters with a null value are ignored in matching records.

If necessary use this script to test the package from SQL*Plus

Step through the following VB code and view the results in the immediate window
The following code should be pasted into a code module in Visual Basic. From the Project menu create a reference to the Microsoft ActiveX Data Objects Library, and ensure the Startup Object is 'Sub Main'. Modify the code to set different input parameters and view the results in the Immediate window. The program contains no graphical interface. Adding one is left as an 'exercise for the reader'.