Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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 "OK" for the aggregated column, where the result value is NULL for the view.

↑ Back to the top


Resolution

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

↑ Back to the top


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 = "Provider=MSDAORA;Data Source=ora_server;User Id=demo;Password=demo"
            .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 = "SELECT * FROM TestView1"
            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 = "SELECT * FROM TestView2"
            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.

↑ Back to the top


Keywords: KB267455, kbprb, kboracle, kbdatabase

↑ Back to the top

Article Info
Article ID : 267455
Revision : 5
Created on : 12/8/2003
Published on : 12/8/2003
Exists online : False
Views : 382