2
OraOLEDB Features

This chapter discusses components of Oracle Provider for OLE DB (OraOLEDB) and how to use the components to develop OLE DB consumer applications. The following topics are discussed:

OraOLEDB Provider Specific Features

Provider-specific features of OraOLEDB objects are described in the following sections:

Additional provider-specific information is provided in Appendix A, "Provider-Specific Information".

Data Source Objects

A data source object in OraOLEDB is responsible for establishing the first connection to the Oracle database. To establish the initial connection, the consumer must use the CoCreateInstance function to create an instance of the data source object. This function requires important information about the provider: class ID of the provider and executable context. The class ID of OraOLEDB is CLSID_OraOLEDB.

OraOLEDB is an in-process server. When calling CoCreateInstance, use the CLSCTX_INPROC_SERVER macro. For example:

// create and instance of OraOLEDB data source object and 
// obtain the IDBInitialize interface
hr = CoCreateInstance(CLSID_OraOLEDB, NULL, 
                      CLSCTX_INPROC_SERVER, IID_IDBInitialize, 
                     (void**)&pIDBInitialize);

Note:
\OraOLEDB does not support persistent data source objects.

After the successful creation of an instance of a data source object, the consumer application can initialize the data source and create sessions.

OraOLEDB supports connections to Oracle databases release 7.3.4 and higher. To connect to a specific database, the consumer is required to set the following properties of the DBPROPSET_DBINIT property set:

DBPROP_AUTH_USERNAME with the user Id, such as scott
DBPROP_AUTH_PASSWORD with the password, such as tiger
DBPROP_INIT_DATASOURCE with the Net8 connect string, such as myOraDb

The consumer could also populate DBPROP_INIT_PROMPT with DBPROMPT_PROMPT which causes the provider to display a logon box for the user to enter the connect information.

Using DBPROMPT_NOPROMPT disables displaying of the logon box. In this case, incomplete logon information causes the provider to return a logon error. However, if this property is set to DBPROMPT_COMPLETE or DBPROMPT_COMPLETEREQUIRED, the logon box will only be displayed if the logon information is incomplete.

Connecting to an Oracle Database

To connect to an Oracle database using OraOLEDB, the OLE DB connection string must be as follows:

"Provider=OraOLEDB.Oracle;User ID=user;Password=pwd;
          Data Source=constr;"

When connecting to a remote database, Data Source must be set to the appropriate Net8 connect string which is the alias in the tnsnames.ora file. For more information on Net8, refer to Net8 Administrator's Guide.

OraOLEDB-specific Connection String Attributes

OraOLEDB offers provider-specific Connection String attributes, which are set the same way as the Provider and User ID are set. The provider-specific connection string attributes are:

FetchSize - specifies the size of the fetch array in rows. See "OraOLEDB-specific Connection String Attributes for Rowsets".
CacheType - specifies the type of cache used to store the rowset data on the client. See "OraOLEDB-specific Connection String Attributes for Rowsets".
ChunkSize - specifies the size of LONG or LONG RAW column data stored in provider-specific cursor. See "OraOLEDB-specific Connection String Attributes for Rowsets".
OSAuthent - specifies whether OS Authentication will be used when connecting to an Oracle database. See "OraOLEDB-specific Connection String Attributes for Data Source Objects".
PLSQLRSet - enables or disables parsing of PL/SQL stored procedures to check if a PL/SQL stored procedure returns a rowset. See "OraOLEDB-Specific Connection String Attributes for Commands".
PwdChgDlg - enables or disables displaying the password change dialog box when the password expires. See "OraOLEDB-specific Connection String Attributes for Data Source Objects".

Default Attribute Values

The default values for these attributes are located under the \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDB registry key.

The registry default values are read by OraOLEDB from the registry when the provider is loaded into memory. If Oracle-specific connection string attributes are not provided at connection time, the default registry values are used. However, if the attributes are provided, these new values override the default registry values. Note that these attributes cannot be changed through the OLE DB property interfaces. After they are set at connection time, they cannot be changed during the lifetime of the session.

These attributes can be set by setting the DBPROP_INIT_PROVIDERSTRING property, provided in the DBPROPSET_DBINIT property set. For example:

"FetchSize=100;CacheType=Memory;OSAuthent=0;PLSQLRSet=1;"

OraOLEDB-specific Connection String Attributes for Data Source Objects

The OSAuthent attribute specifies whether OS authentication will be used when connecting to an Oracle database. Valid values are 0 (disabled) and 1 (enabled). The default is 0 which indicates that OS authentication is not used.

OS Authentication

OS authentication is the feature by which Oracle uses the security mechanisms of the operating system to authorize users. For more information on this subject and how to set it up on Windows NT clients, refer to the information on authenticating database users on Windows NT in Oracle8i Administrator's Guide for Windows NT.

After the Windows NT client has been set up properly for OS authentication, this feature may be enabled by OraOLEDB clients by setting any of the following:

DBPROP_AUTH_USERNAME to "/"
DBPROP_INIT_PROVIDERSTRING to "OSAuthent=1;"
OSAuthent in the registry to "1"

Example: Connecting to an Oracle Database Using ADO

The following are examples illustrating how to connect to an Oracle database using OraOLEDB and ADO.

Note:
If the Data Source, User ID, and Password are provided with the Open method, ADO ignores those ConnectionString attributes.

Connect using ConnectionString
Dim con As ADODB.Connection Set con = NEW ADODB.Connection con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyOraDb;" & _ "User ID=scott;Password=tiger;" con.Open
Connect without using ConnectionString
Dim con As ADODB.Connection Set con = NEW ADODB.Connection con.Provider = "OraOLEDB.Oracle" con.Open "MyOraDb", "scott", "tiger"
Connect and set provider specific attributes
Dim con As ADODB.Connection Set con = NEW ADODB.Connection con.Provider = "OraOLEDB.Oracle" con.ConnectionString = "FetchSize=200;CacheType=Memory;" & _ "OSAuthent=0;PLSQLRSet=1;Data Source=MyOraDb;" & _ "User ID=scott;Password=tiger;" con.Open
OS Authenticated connect setting user id to "/"
Dim con As ADODB.Connection Set con = NEW ADODB.Connection con.Provider = "OraOLEDB.Oracle" con.Open "MyOraDb", "/", ""
OS Authenticated connect using OSAuthent
Dim con As ADODB.Connection Set con = NEW ADODB.Connection con.Provider = "OraOLEDB.Oracle" con.ConnectionString = "Data Source=MyOraDb;OSAuthent=1;" con.Open

Password Expiration

Oracle8i provides a Password Expiration feature which allows database administrators to force users to change their passwords regularly. The PwdChgDlg attribute enables or disables the displaying of the password change dialog, whenever a logon fails due to an expired password. When enabled, the provider displays the dialog to change the password. When disabled, the logon fails with an error message. The valid values are 0 (disabled) and 1 (enabled). The default is 1 (enabled). For more information on the Password Expiration feature, see Oracle8i Administrator's Guide.

Sessions

OraOLEDB session object represents a single connection to an Oracle Server. The session object exposes the interfaces that allow data access and manipulation.

The first session created on the initialized data source inherits the initial connection established by IDBInitialize::Initialize(). Subsequent sessions that are created establish their own independent connections to the particular Oracle server specified by the data source properties.

Each session object also defines a transaction space for a data source. All command and rowset objects created from a particular session object are part of the transaction of that session.

After all references to the session object are released, the session object is removed from memory and the connection is dropped.

Transactions

OraOLEDB supports local and distributed transactions which provide explicit commit and abort.

OraOLEDB does not support nested transactions. In addition, a local transaction cannot be started if the session is currently enlisted in a distributed transaction. This also applies to distributed transactions if the session is currently enlisted in a local transaction.

Local Transactions

OraOLEDB supports the ITransactionLocal interface for explicit transactions. By default, OraOLEDB is in an autocommit mode, meaning that each unit of work done on the database is automatically or implicitly committed. With the use of ITransactionLocal interface, consumers may explicitly start a transaction for a particular session, allowing a unit of work to be explicitly committed or aborted by the consumer.

OraOLEDB supports the Read Committed (Cursor Stability) isolation level. In this level, the changes made by other transactions are not visible until those transactions are committed.

Distributed Transactions

OraOLEDB consumers must install Oracle Services for Microsoft Transaction Server (MTS) release 8.1.5.0.1 or later to be able to participate in Microsoft Transaction Server transactions or to enlist in a global transaction coordinated by Microsoft Distributed Transaction Coordinator (MS DTC). For setup and configuration information on Oracle Services for MTS, see Using Microsoft Transaction Server With Oracle8.

OraOLEDB ignores IsoLevel, IsoFlags, and pOtherOptions parameters when ITransactionJoin::JoinTransaction() is called. These options must be provided when the consumer acquires a transaction object from MS DTC with the ITransactionDispenser::BeginTransaction() method call.

However, if IsoFlags is non-zero, XACT_E_NOISORETAIN is returned.

Commands

OraOLEDB supports both the ANSI SQL supported by Oracle and the ODBC SQL syntax.

Stored Procedures

When executing an Oracle stored procedure using a command, use Oracle native syntax or the ODBC procedure call escape sequence in the command text:

Oracle native syntax: BEGIN credit_account(123, 40); END;
ODBC syntax: {CALL credit_account(123, 40)}

Preparing Commands

OraOLEDB validates and fetches the metadata only for SELECT SQL statements.

Command Parameters

When using Oracle ANSI SQL, parameters in the command text are preceded by a colon. In ODBC SQL, parameters are indicated by a question mark ("?").

OraOLEDB supports input, output, and input/output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.

The following limitations apply to the use of command parameters:

NLOB, NCHAR, and NVARCHAR2 data types are not supported for command parameters. BLOB and CLOB data types are not supported for PL/SQL stored procedure parameters.
OraOLEDB supports only positional binding.

OraOLEDB-Specific Connection String Attributes for Commands

The PLSQLRSet attribute specifies whether OraOLEDB needs to parse the PL/SQL stored procedures to determine if a PL/SQL stored procedure returns a rowset. If any of the PL/SQL blocks that the consumer is going to execute returns a rowset, PLSQLRSet needs to be set to 1.

This parsing process incurs processing overhead of at least one round trip to the database. If none of the PL/SQL stored procedures return a rowset within a particular session, PLSQLRSet should be set to 0. The default value of this attribute is 0.

Stored Procedures and Functions Returning Rowsets

Oracle Provider for OLE DB allows consumers to execute a PL/SQL stored procedure with an argument of Ref cursor type or a stored function returning a Ref cursor. OraOLEDB can return up to one rowset per PL/SQL stored procedure or function call.

OraOLEDB returns a rowset for the Ref cursor bind variable. Because there is no predefined datatype for Ref cursors in the OLE DB specification, the consumer must not bind this parameter.

If the PL/SQL stored procedure has one or more arguments of Ref cursor type, OraOLEDB binds these arguments appropriately and returns a rowset for the first argument of Ref cursor type.

If the PL/SQL stored function returns a Ref cursor or has an argument of Ref cursor type, OraOLEDB binds these appropriately and returns a rowset. If the stored function does not return a Ref cursor, the rowset is returned for the first Ref cursor argument in the stored function.

To use this feature, stored procedures or functions must be called in the ODBC SQL escape sequence. This feature also requires stored procedures and functions to be in a specific format:

A package must be created to define all the cursors used in the procedure or function.
The procedure or function is created using the defined cursors.

Example: Stored Procedure Returning Result Set

The following sample code shows an ADO program calling a PL/SQL stored procedure returning a result set.

PL/SQL Package
CREATE OR REPLACE PACKAGE Employees AS TYPE empcur IS REF CURSOR; PROCEDURE GetEmpRecords(p_cursor OUT empcur, indeptno IN NUMBER, p_errorcode OUT NUMBER); FUNCTION GetDept(inempno IN NUMBER, p_errorcode OUT NUMBER) RETURN empcur; END Employees; CREATE OR REPLACE PACKAGE BODY Employees AS PROCEDURE GetEmpRecords(p_cursor OUT empcur, indeptno IN NUMBER, p_errorcode OUT NUMBER) IS BEGIN p_errorcode := 0; OPEN p_cursor FOR SELECT * FROM emp WHERE deptno = indeptno ORDER BY empno; EXCEPTION WHEN OTHERS THEN p_errorcode:= SQLCODE; END GetEmpRecords; FUNCTION GetDept(inempno IN NUMBER, p_errorcode OUT NUMBER) RETURN empcur IS p_cursor empcur; BEGIN p_errorcode := 0; OPEN p_cursor FOR SELECT deptno FROM emp WHERE empno = inempno; RETURN (p_cursor); EXCEPTION WHEN OTHERS THEN p_errorcode:= SQLCODE; END GetDept; END Employees;
ADO Program
Dim Con As New ADODB.Connection Dim Rst1 As New ADODB.Recordset Dim Rst2 As New ADODB.Recordset Dim Cmd As New ADODB.Command Dim Prm1 As New ADODB.Parameter Dim Prm2 As New ADODB.Parameter Con.Provider = "OraOLEDB.Oracle" Con.ConnectionString = "PLSQLRSet=1;Data Source=MyOraDb;" & _ "User ID=scott;Password=tiger;" Con.Open Cmd.ActiveConnection = Con Set Prm1 = Cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30) Cmd.Parameters.Append Prm1 Set Prm2 = Cmd.CreateParameter("Prm2", adSmallInt, adParamOutput) Cmd.Parameters.Append Prm2 ' Although Employees.GetEmpRecords() takes three parameters, only ' two need to be bound because Ref cursor parameters are automatically ' bound by the provider. ' Stored Procedures returning result sets must be called using the ' ODBC escape sequence for calling stored procedures. Cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}" Set Rst1 = Cmd.Execute ' Just as in a stored procedure, the Ref cursor return value must ' not be bound in a stored function. ' Stored Functions returning result sets must be called using the ' ODBC escape sequence for calling stored functions. Cmd.CommandText = "{CALL Employees.GetDept(?, ?)}" Set Rst2 = Cmd.Execute

Rowsets

Note:
Dynamic and Keyset cursors are not supported by Oracle Provider for OLE DB.

Creating Rowsets

OraOLEDB supports IOpenRowset::OpenRowset and ICommand::Execute for creating rowsets.

Creating a Rowset with IOpenRowset::OpenRowset

When using IOpenRowset::OpenRowset, note the following guidelines:

The pTableID parameter must contain a DBID structure that specifies a base table or a view.
The DBID structure's eKind member must be set to DBKIND_GUID_NAME, DBKIND_NAME, or DBKIND_PGUID_NAME.
The DBID structure's uName member must specify the base table or view name as a Unicode character string. It cannot be NULL.
The pIndexID parameter of OpenRowset must be NULL.

Creating a Rowset with ICommand::Execute

OraOLEDB supports SQL SELECT statements that return rowsets. OraOLEDB also supports returning rowsets from PL/SQL stored procedures and functions.

By default, ADO creates a non-updatable rowset from a command object. An updatable rowset can be created by setting the Updatability and IRowsetChange properties on the command object. The Updatability property can be set to the following values:

1

update

2

delete

3

update and delete

4

insert

5

insert and update

6

insert and delete

7

insert, delete, and update

The following ADO code sample sets the Updatability property on a command object to allow insert, delete, and update operations on the rowset object.

Dim Cmd As New ADODB.Command
Dim Rst As New ADODB.Recordset
Dim Con As New ADODB.Connection
...
Cmd.ActiveConnection = Con
Cmd.CommandText = "SELECT * FROM emp"
Cmd.CommandType = adCmdText
cmd.Properties("IRowsetChange") = TRUE
Cmd.Properties("Updatability") = 7
'creates an updatable rowset
Set Rst = cmd.Execute

Updatability

OraOLEDB supports both immediate and deferred update mode. However, insert and update operations cannot be deferred when the operation changes a non-scalar column, such as LONGs, BLOBs, or CLOBs. When non-scalar column values are changed in a deferred update mode, the entire row is transmitted to the database as though the operation was in an immediate update mode. In addition, these operations cannot be undone with the Undo method (ADO) or IRowsetUpdate::Undo(). But if they are in a transaction, they can be rolled back with RollbackTrans method (ADO) or ITransactionLocal::Abort().

Rowsets that are created using queries with JOINs are not updatable by Oracle Provider for OLE DB. However, these rowsets could be made updatable for ADO users by specifying CursorLocation as adUseClient.

Server Data on Insert Property

If DBPROP_SERVERDATAONINSERT (Server Data on Insert) is set to TRUE using OraOLEDB, the consumer can obtain defaults, sequences, and triggered column values from newly inserted and updated rows, provided that the insert and update operations are made through the rowset.

Having DBPROP_SERVERDATAONINSERT set to TRUE may degrade performance for both insert and update executions using a rowset because OraOLEDB fetches row data from the database for the newly inserted and updated row. However, if DBPROP_SERVERDATAONINSERT is set to its default value of FALSE, only the explicitly provided values for insert and update operations get returned when column values are requested for those rows.

If the base table from which the rowset was created does not contain any defaults, sequences, or triggers, it is highly recommended that DBPROP_SERVERDATAONINSERT retain its default value of FALSE.

The DBPROP_SERVERDATAONINSERT property does not affect the performance of insert and update executions using the command object.

Searching for Rows with IRowsetFind::FindNext

OraOLEDB only supports searches performed on CHAR, DATE, FLOAT, NUMBER, RAW, and VARCHAR2 columns. Otherwise, DB_E_NOTSUPPORTED is returned.

When a search is done with a NULL value, only the DBCOMPAREOPS_EQ and DBCOMPAREOPS_NE compare operations are supported. Otherwise, DB_E_NOTSUPPORTED is returned.

OraOLEDB-specific Connection String Attributes for Rowsets

OraOLEDB-specific connection string attributes which affect the performance of the rowset are:

CacheType - specifies the type of caching used by the provider to store rowset data. OraOLEDB provides two caching mechanisms:
- Memory - The provider stores all the rowset data in-memory. This caching mechanism provides better performance at the expense of higher memory utilization. The default is Memory.
- File - The provider stores all the rowset data on-disk. This caching mechanism limits the memory consumption at the expense of performance.
ChunkSize - is currently reserved.
FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

The default attributes values are set in the registry. For more information, see "Default Attribute Values". The following ADO code example overrides the default attribute values:

Dim con As ADODB.Connection
Set con = NEW ADODB.Connection
con.ConnectionString = "Provider=OraOLEDB.Oracle;User ID=scott;" & _
                          "Password=tiger;Data Source=MyOraDB;" & _
                          "FetchSize=200;CacheType=File;"

con.Open

Tips for ADO Programmers

Setting the ADO Rowset property LockType to adLockPessimistic is not supported by Oracle Provider for OLE DB. If LockType is set to adLockPessimistic, OraOLEDB behaves similar to when set as adLockOptimistic. This behavior occurs because OraOLEDB does not perform explicit locks on the rows being modified. However, when the new data is submitted to the database, it only performs the update if the rowset data was not already updated by another user, which means that dirty writes are not allowed. LockType values adLockReadOnly, adLockBatchOptimistic, and adLockOptimistic are supported by OraOLEDB.

Setting ADO Rowset property CursorType to adOpenKeyset or adOpenDynamic is not supported by Oracle Provider for OLE DB. OraOLEDB does not support either of the two as Oracle supports Read Consistency, which ensures that the data returned by a query contains only committed data as of the time the query was executed. CursorType values adOpenStatic and adOpenForwardOnly are supported by OraOLEDB.

Schema Rowsets

The schema rowsets available through Oracle Provider for OLE DB are:

DBSCHEMA_COLUMNS
DBSCHEMA_INDEXES
DBSCHEMA_SCHEMATA
DBSCHEMA_VIEWS
DBSCHEMA_TABLES
DBSCHEMA_PROVIDER_TYPES (forward scroll only)
DBSCHEMA_FOREIGN_KEYS
DBSCHEMA_PRIMARY_KEYS
DBSCHEMA_PROCEDURES
DBSCHEMA_PROCEDURE_PARAMETERS

Date Formats

The date format for the Oracle session cannot be set using ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, the date formats are controlled by the Regional Settings properties in the Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

For Oracle Provider for OLE DB, NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle as a string, the date needs to be in the 'YYYY-MM-DD HH24:MI:SS' format. For example:

SELECT * FROM EMP WHERE HIREDATE > '1981-06-15 17:32:12'

To use a different format, you need to use the SQL function, TO_DATE(), to specify the format for dates passed as strings. For example:

SELECT * FROM EMP WHERE HIREDATE > TO_DATE('15-JUN-81', 'DD-MON-YY')

However, for dates passed as parameters, the date format is controlled by ADO, which is controlled by the Regional Settings in the Windows Control Panel. In this case, TO_DATE() should not be used. For example:

Private Sub Command1_Click()
  Dim objCon As New ADODB.Connection
  Dim objCmd As New ADODB.Command
  Dim objRst  As New ADODB.Recordset
  Dim pDate As New ADODB.Parameter

  objCon.Provider = "OraOLEDB.Oracle"
  objCon.Open "db815", "scott", "tiger"
  Set pDate = objCmd.CreateParameter("pDate", adDate, adParamInput)
  objCmd.Parameters.Append pDate
  objCmd.CommandText = _
         "SELECT * FROM EMP WHERE HIREDATE > ?"
  objCmd.ActiveConnection = objCon
  objCmd.CommandType = adCmdText
  pDate.Value = "06/15/1981"
  Set objRst = objCmd.Execute

    ...
End Sub

LOB Support

The ISequentialStream interface is supported for all LONG, LONG RAW, and LOB (BLOB, CLOB, NCLOB, and BFILE) columns. The consumer can use this interface to read and write to all the LOB columns, except BFILE which is read only. To have read and write access to these columns, the SELECT SQL statement used to create the rowset should not contain a JOIN.

Note: Although most of the LOB columns in an Oracle database support up to 4 GB of data storage, ADO limits the maximum column size to 2 GB.

For more information on LOBs, see Oracle8i Application Developer's Guide - Large Objects (LOBs).

Errors

OLE and COM objects report errors through the HRESULT return code of the object member functions. An OLE/COM HRESULT is a bit-packed structure. OLE provides macros that dereference structure members. OraOLEDB exposes IErrorLookup to retrieve information about an error.

All objects support extended error information. For this, the consumer needs to instantiate the OLE DB Extended Error object followed by calling the method GetErrorDescription() to get the error text.

// Instantiate OraOLEDBErrorLookup and obtain a pointer to its
//   IErrorLookup interface
CoCreateInstance(CLSID_OraOLEDBErrorLookup, NULL, CLSCTX_INPROC_SERVER,
                 IID_IErrorLookup, (void **)&pIErrorLookup)
//Call the method GetErrorDescription() to get the full error text
pIErrorLookup->GetErrorDescription()

The OraOLEDB provider returns the entire error stack in one text block.

For ADO users, the following example applies:

Dim oerr As ADODB.Error
For Each oerr in con.Errors
    MsgBox "Error: " & oerr.Description & vbCrLf _
        & "Source: " & oerr.Source
Next

Example: Using OraOLEDB with Visual Basic

This simple example illustrates how to use Oracle Provider for OLE DB with Visual Basic 6.0 to connect to an Oracle database and execute PL/SQL stored procedures and functions.

Setting up the Oracle Database

This example assumes that the Oracle database has the demonstration table EMP under the user account SCOTT. The SCOTT account is included in the Oracle starter database. If the account does not exist on your database, create the account before running the sample program. If your database does not contain the EMP table, you can use the demobld.sql script to create the demonstration tables.

This example also uses exampledb as the database network alias when connecting to the Oracle database. You will need to change this network alias to match your system.

To build the sample tables:

Start SQL*Plus.
Connect as user name SCOTT with the password TIGER.

Run the demobld.sql script:

SQL> @oracle_base\oracle_home\dbs\demobld.sql;

After the EMP table has been created in the SCOTT account, you need to create the PL/SQL package that contains the stored procedure and function that are run in the Visual Basic example.

To create the PL/SQL package:

Start SQL*Plus.
Connect as user name SCOTT with the password TIGER.
Create the PL/SQL packages shown in "PL/SQL Package".

Note:
When creating PL/SQL packages the / character is used as a terminator and must be added on a separate line following each CREATE PACKAGE..END block.

Setting up the Visual Basic Project

After the Oracle database setups are completed, you can create the Visual Basic 6.0 project.

Start Visual Basic 6.0 and create a new project.
Make sure that the Microsoft ActiveX Data Objects 2.1 Library and Microsoft ActiveX Data Objects Recordset 2.1 Library are included as Project References.
Add two commands buttons to the form. One of the buttons will run the code to execute the PL/SQL procedure GetEmpRecords. The other will run the code to execute the PL/SQL function GetDept.

Add the following code to Click subroutine of the button that will run the code to execute the PL/SQL procedure GetEmpRecords.

Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
Dim Message, Title, Default, EmpNoValue

Message = "Enter an employee number (5000 - 9000)"
Title = "Choose an Employee"
Default = "7654"

On Error GoTo err_test

EmpNoValue = InputBox(Message, Title, Default)
If EmpNoValue = "" Then Exit Sub
If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=exampledb;" & _
                          "User ID=scott;" & _
                          "Password=tiger;" & _
                          "PLSQLRSet=1;"
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
                                 EmpNoValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2

cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
Set recset = cmd.Execute
MsgBox "Number: " & EmpNoValue & "  Dept: " & recset.Fields("deptno").Value

Exit Sub

err_test:
    MsgBox Error$
    For Each objErr In Oracon.Errors
        MsgBox objErr.Description
    Next
    Oracon.Errors.Clear
    Resume Next

Add the following code to Click subroutine of the button that will run the code to execute the PL/SQL function GetDept.

Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error
 
Dim Message, Title, Default, DeptValue
Message = "Enter a department number (10, 20, or 30)"
Title = "Choose a Department"
Default = "30"

On Error GoTo err_test
DeptValue = InputBox(Message, Title, Default)
If DeptValue = "" Then Exit Sub
If DeptValue < 10 Or DeptValue > 30 Then DeptValue = 30

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=exampledb;" & _
                          "User ID=scott;" & _
                          "Password=tiger;" & _
                          "PLSQLRSet=1;"
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
                                DeptValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2

cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
Set recset = cmd.Execute
Do While Not recset.EOF
   MsgBox "Number: " & recset.Fields("empno").Value & "  Name: " &
   recset.Fields("ename").Value & "  Dept: " & recset.Fields("deptno").Value
   recset.MoveNext
Loop

Exit Sub

err_test:
    MsgBox Error$
    For Each objErr In Oracon.Errors
        MsgBox objErr.Description
    Next
    Oracon.Errors.Clear
    Resume Next

Run the project and check the results. For example, if you choose the Get Employee Records by Dept button, you would get the following dialogs:

1	update
2	delete
3	update and delete
4	insert
5	insert and update
6	insert and delete
7	insert, delete, and update

2 OraOLEDB Features