How to use ADO to access Oracle database stored procedures

One, about ADO

In a database environment based on client/server architecture, data can be accessed through OLE DB interfaces, but it defines low-level COM interfaces that are not only difficult to use, but are not accessible by advanced programming tools such as Vb,vba,vbscript.

Using ADO makes it easy to access data directly from programming languages such as VB (via OLE DB interfaces). ADO is based on an object-oriented approach, and its object model is shown in the following illustration (abbreviated)

As the above illustration shows, the ADO object model consists of a total of six objects, much simpler than the data Access object (DAO). Therefore, it is often used in practice to access the database.

Second, ADO access to the database instance

Let's take Oracle, for example, to use VB6.0 to access stored procedures in its database. In this example, we first create two stored procedures on the Oracle database, one with no parameters and the other with parameters. Then, use ADO to access both of these stored procedures. The steps are as follows:

1. Run the following DDL script on the Oracle server:

　　DROP TABLE person;
　　 CREATE TABLE person
　　 (ssn NUMBER(9) PRIMARY KEY,
　　 fname VARCHAR2(15),
　　 lname VARCHAR2(20));
　　 INSERT INTO person VALUES(555662222,'Sam','Goodwin');
　　 INSERT INTO person VALUES(555882222,'Kent','Clark');
　　 INSERT INTO person VALUES(666223333,'Jane','Doe');
　　 COMMIT;
　　 /

2. Create a package on an Oracle Server (package):

　　CREATE OR REPLACE PACKAGE packperson
　　 AS
　　 TYPE tssn is TABLE of NUMBER(10)
　　 INDEX BY BINARY_INTEGER;
　　 TYPE tfname is TABLE of VARCHAR2(15)
　　 INDEX BY BINARY_INTEGER;
　　 TYPE tlname is TABLE of VARCHAR2(20)
　　 INDEX BY BINARY_INTEGER;
　　
　　 PROCEDURE allperson
　　 (ssn OUT tssn,
　　 fname OUT tfname,
　　 lname OUT tlname);
　　 PROCEDURE oneperson
　　 (onessn IN NUMBER,
　　 ssn OUT tssn,
　　 fname OUT tfname,
　　 lname OUT tlname);
　　 END packperson;
　　 /