DOCUMENT:Q190934  11-SEP-2001  [odbc]
TITLE   :FIX: Degree of Derived Table Error When Updating Oracle Data
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER::Build 2.73.7269,Build 2.73.7283.01,Build 2.73.7283.03,Build 2.73.7356
OPER/SYS:
KEYWORDS:kbATM kbDatabase kbDriver kbMFC kbODBC kbOracle kbSDKDataAc200fix kbGrpDSVCDB kbGrpDSMD

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft ODBC for Oracle version 2.0, versions Build 2.73.7269, Build 2.73.7283.01, Build 2.73.7283.03, Build 2.73.7356 
-------------------------------------------------------------------------------

SYMPTOMS
========

When trying to UPDATE a recordset with the Microsoft Foundation Classes (MFC)
ODBC classes you may get the following error:

   [Microsoft][ODBC driver for Oracle]Degree of derived table does not match
   column list.

CAUSE
=====

The Microsoft Oracle ODBC driver is not looking for quoted column names when
trying to match them for parsed column names.

RESOLUTION
==========

The new Microsoft Oracle ODBC driver version 2.573.2927 or higher fixes this
problem. The latest Microsoft Oracle ODBC can be downloaded as part of the
Microsoft Data Access Components from the following Web address:

   http://www.microsoft.com/data/

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products listed at the
beginning of this article. This bug has been corrected in Microsoft's Oracle
ODBC driver, version 2.573.2927 or higher.

MORE INFORMATION
================

MFC ODBC is using SQLSetPos for the update. This error has also been seen with
ActiveX Data Objects (ADO) for the same reasons.

Steps to Reproduce Behavior
---------------------------

Run the three following DDL statements in SQL*Plus or ODBCTest against the Oracle
server:

      CREATE TABLE zeric (CNTR NUMBER(4),
                          FNAME VARCHAR2(10),
                          LNAME VARCHAR2(20));
      CREATE INDEX zeric_cntr ON zeric (CNTR);
      INSERT INTO zeric VALUES (1, 'Eric', 'Burgess');

The above code is written for SQL*Plus. If you want to do this in ODBCTest,
remove the semicolons.

In ODBCTest, run the following code. Only the parameters that are manually
selected are shown here. Leave all other parameters alone.

   1. Full Connect:
      Full Connect(Use Driver)

   2. SQLSetStmtAttr:
      Attribute = SQL_ATTR_CURSOR_TYPE=6
      ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1

   3. SQLSetStmtAttr:
      Attribute = SQL_ATTR_CONCURRENCY=7
      ValuePtr = SQL_CONCUR_VALUES=4

   4. Type the following SQL Statement in the Input window. Make sure to use
      the double quotes around the field names.

         SELECT "CNTR", "FNAME", "LNAME" FROM "ZERIC"

   5. Call SQLPrepare.

   6. Call SQLExecute.

   7. Select Bind Col All.

   8. Select SQLExtendedFetch:
      FetchOrientation = SQL_FETCH_NEXT=1
      FetchOffset = 1

   9. Select SQLExtendedFetch:
      FetchOrientation = SQL_FETCH_FIRST=2
      FetchOffset = 1

   10. Change the FNAME field contents. This should be done through the grid
       that opens.

   11. Call SQLSetPos:
       Operation = SQL_UPDATE=2,
       LockType = SQL_LOCK_NO_CHANGE=0

SQLSetPos call causes the error.

   SqlState = "21S02" MessageText = "[Microsoft][ODBC driver for Oracle]Degree
   of derived table does not match column list"

NOTE: The classes in Visual C++ MFC ODBC call these ODBC APIs when they attempt
to update a record, and that causes the same error message.

REFERENCES
==========

For additional information, please see the following article in the Microsoft
Knowledge Base:

   Q178044 PRB: Oracle Errors When Using Query-Based Updates Through ADO

Additional query words:

======================================================================
Keywords          : kbATM kbDatabase kbDriver kbMFC kbODBC kbOracle kbSDKDataAc200fix kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbGrpDSODBC 
Technology        : kbAudDeveloper kbODBCSearch
Version           : :Build 2.73.7269,Build 2.73.7283.01,Build 2.73.7283.03,Build 2.73.7356
Issue type        : kbbug
Solution Type     : kbfix

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.