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.

2.3 ADO and OLE DB relationships

OLE DB is a system-level programming interface, and ADO is an application-level programming interface. ADO is a consumer of OLE DB that provides access to OLE DB data sources at the application level. ADO provides an easy to use application-level interface for OLE DB. ADO allows users to write applications that access and manipulate data in the database server through an OLE DB provider. The main advantages are ease of use, high speed, low memory, and less disk space. The relationship between ADO and OLE DB is shown in Figure 2:

3 definition of stored procedures for returning recordsets in Oracle

In an Oracle stored procedure, you can return a recordset by defining an outgoing parameter of type ref CURSOR (cursor pointer). OLE DB allows a consumer to perform a pl/sql stored procedure with a REF CURSOR type parameter, or to perform a stored function that returns a REF CURSOR. The stored procedure or function that returns the recordset must be defined in the package. A package is an object of an Oracle database that encapsulates data types, stored procedures, functions, variables, and constants. The package is divided into two parts: Baotou and package, which should be established separately. Headers are used to define elements that can be referenced externally, and the package body defines the actual code. When you use a package definition to return a stored procedure for a recordset, you need to predefined your own REF CURSOR type in Baotou. In the package body, a stored procedure or function must use a predefined cursor type. In addition, in a stored procedure or function, you can define parameters for multiple REF CURSOR types to return more than one recordset.

The following is an example of a worker's Table EMP, which describes how to define an Oracle stored procedure that returns a recordset, as defined by the EMP table:

CREATE TABLE emp(
empno NUMBER(4) NOT NULL, --职工编号
ename VARCHAR2(10), --职工姓名
hiredate DATE, --雇佣日期
sal NUMBER(7,2), --工资
deptno NUMBER(2))； --所属部门编号