Oracle stored procedures that return recordsets using OLE DB and ADO calls

Abstract OLE DB is an open specification built on the success of ODBC, which provides an open standard for accessing and manipulating different types of data. ADO is a consumer of old db that provides access to OLE DB data sources at the application level. Using OLE DB and ADO in your application, you can efficiently invoke an Oracle stored procedure that returns a recordset.

Keyword OLE DB ADO stored procedure recordset

1 Preface

In the process of developing an ADO application based on Oracle database, in order to improve execution speed and reduce network traffic, it is often necessary to call the Oracle database server-side stored procedures in the application. Some stored procedures need to return a multiple-row recordset. In this case, it is difficult to call a stored procedure that returns a recordset in your application. This article describes how to use OLE DB and ADO to invoke an Oracle stored procedure that returns a recordset.

2 The relationship between OLE DB and ADO

Introduction to 2.1 OLE DB

OLE DB is an open specification based on ODBC success, which provides an open standard for accessing and manipulating different types of data. OLE DB defines a collection of COM interfaces that encapsulates a variety of database management system services. These interfaces allow you to create software components that implement these services. OLE DB components include data providers (storing and publishing data), data consumers (working with data), and service components (processing and transferring data).

The design of OLE db is centered on the concept of consumer and provider. OLE db consumers represent the traditional client side, where the provider passes data to the consumer in tabular form. The Oracle Provider for OLE DB (ORAOLEDB) enables OLE DB consumers to efficiently access Oracle data sources. Figure 1 shows the system diagram for OLE DB. The OLE DB data provider passes data from the data source to the consumer. On the basis of standard interfaces, OLE DB consumers have access to data from providers. Because there are COM components, consumers can access data in any programming language that supports COM.

2.2 ADO Object modeling in the ADO object model, Connection, command, and Recordset objects are three main objects. The Connection object represents a connection to remote data. The connection object can be associated with a command object or a Recordset object. The Command object defines the specified command to be executed on the data source. Command objects can be used to execute commands and parameterized SQL statements that can be used for SQL statements and for SQL queries that return recordsets. The command object can either use an active Connection object or create its own connection to the destination data source. The command object contains a parameters collection in which each parameter object represents the parameters used by the Command object. When a command object executes a parameterized SQL statement, each parameter object represents an argument in the SQL statement. The complete collection of records represented by a Recordset object from a basic table or command execution result. The Recordset object can either use an active Connection object or create its own connection to the destination data source. The Recordset object allows you to query and modify data. Each recordset contains a collection of Field objects, where each Field object represents a data column in the recordset.