Microsoft KB Archive/267455

= PRB: Problems Encountered When You Use Oracle Aggregate Functions on Non-Null Columns With Client-Side Cursors =

Article ID: 267455

Article Last Modified on 12/8/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q267455



SYMPTOMS
When you use aggregate functions like DECODE on Oracle views derieved from tables with non-null columns, NULL values can be returned. Attempting to query the value of the column or trying to perform a MoveFirst causes the following error message:

Run-time error '-2147467259 (80004005)': The data provider or other service returned an E_FAIL status.

NOTE: This behavior occurs on Oracle 8.x servers, and not on Oracle 7.3.x servers.



CAUSE
This behavior occurs as a result of a problem in Oracle's client software. A description of the table from Oracle shows that a NULL value is not &quot;OK&quot; for the aggregated column, where the result value is NULL for the view.



RESOLUTION
To work around this behavior, use a server-side cursor.



MORE INFORMATION
Both the Microsoft OLE DB provider for Oracle and the Microsoft Open Database Connectivity (ODBC) for the Oracle driver (from MDAC 2.1.4202.3 or later) are used to reproduce the behavior. This behavior only occurs when you use a client-side cursor, and is not encountered when you use server-side cursors (for example, the NULL value is returned correctly).

Steps to Reproduce the Behavior
  Run the following SQL statements from SQL*PLUS to set up both the tables and the views: CREATE TABLE TESTNULL ( FLD1 VARCHAR2(10) NOT NULL,  FLD2 VARCHAR2(10) NULL );

INSERT INTO TESTNULL (FLD1, FLD2) VALUES ('MARY', 'MANAGER');

CREATE OR REPLACE VIEW TestView1 ( CUSTOMER ) AS SELECT DECODE(FLD1,'JOHN',FLD1,NULL) as CUSTOMER FROM TESTNULL;

CREATE OR REPLACE VIEW TestView2 ( CUSTOMER ) AS SELECT DECODE(FLD2,'JOHN',FLD1,NULL) as CUSTOMER FROM TESTNULL ;   Create a standard Visual Basic EXE that supports ActiveX Data Objects (ADO). In the main form, create two command buttons, and then enter the following code:

Dim cn As New ADODB.Connection Private Sub Form_Load Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset

Set cn = New ADODB.Connection With cn      .ConnectionString = &quot;Provider=MSDAORA;Data Source=ora_server;User Id=demo;Password=demo&quot; .CursorLocation = adUseClient ' If you use adUseServer then no problem is seen ' .CursorLocation = adUseServer .Open End With End Sub

Private Sub Command1_Click 'Selecting FLD1 which does not allow NULLs Set cmd = New ADODB.Command Set rs = New ADODB.Recordset With cmd .ActiveConnection = cn       .CommandText = &quot;SELECT * FROM TestView1&quot; rs.CursorType = adOpenStatic rs.Open cmd End With Debug.Print rs(0)        ' Error will be seen here. End Sub

Private Sub Command2_Click 'Selecting FLD2 which allows NULL Set cmd = New ADODB.Command Set rs = New ADODB.Recordset With cmd .ActiveConnection = cn       .CommandText = &quot;SELECT * FROM TestView2&quot; rs.CursorType = adOpenStatic rs.Open cmd End With Debug.Print rs(0)      ' No error seen. Prints Null in the Immediate window. End Sub



This behavior occurs when the Non-Null column in the original table is manipulated when you use Oracle aggregate functions like DECODE. The behavior can be avoided if you use server-side cursors that ensure the correct result is sent back to the application.

Keywords: kboracle kbdatabase kbprb KB267455

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.