Excel VBA ADO Recordset查询Oracle列数问题：SELECT *执行报错

First off, let's clear up the big question: Microsoft ODBC for Oracle doesn't have a hard limit of 17 columns—this driver can handle far more columns in a single query than that. So the column count itself isn't why you're hitting the -2147467259 (80004005) error. Let's break down the most likely causes and how to debug them:

1. Problematic Column Data Types #

The most common culprit here is one or more columns in your table with data types that the Microsoft ODBC for Oracle driver struggles with. Examples include:

• Large object types like CLOB, BLOB, or LONG RAW
• Custom Oracle data types (e.g., user-defined types, nested tables)
• Legacy types like LONG which have limited support in older ODBC drivers

When you use SELECT *, you're pulling all these columns into your Recordset, but when you specify only a few columns, you're avoiding the problematic ones.

How to debug: Gradually add columns to your SELECT statement (e.g., start with COLUMN1, COLUMN2, COLUMN3 and keep adding until the error pops up). The column that triggers the error is the one causing the issue.

2. Column Content Size Exceeding Driver Buffers #

Another possibility is that one or more columns contain data that's larger than the default buffer size configured for the ODBC driver. For example:

• A VARCHAR2 column with extremely long string values
• A LONG column holding large amounts of text

When you query only a few columns, the driver can handle the smaller data load, but SELECT * combines multiple large columns, leading to a buffer overflow or timeout.

How to fix:

• Open your ODBC Data Source Administrator, find your Oracle DSN, and adjust the "Advanced" settings to increase buffer sizes (look for options like MaxBufferSize).
• Alternatively, try adding FetchSize or MaxRows parameters to your ADO Recordset configuration to limit the amount of data fetched at once.

3. Missing Permissions on Specific Columns #

It's possible you have SELECT access to most columns in the table, but not all. When you run SELECT *, the driver tries to read every column, and hits a permission denial on one of them—resulting in the vague "unspecified error".

How to verify:

• Run DESCRIBE TABLE_NAME in an Oracle client (like SQL*Plus) to list all columns.
• Try selecting individual columns you haven't tested yet (e.g., SELECT COLUMN15 FROM TABLE_NAME) to see if any throw a permission error.
• Check the ALL_TAB_COLUMNS data dictionary view to confirm your user has access to all columns.

4. Outdated or Incompatible ODBC Driver #

Microsoft ODBC for Oracle is a legacy driver that's no longer actively maintained. If you're connecting to a newer version of Oracle (12c+), the driver might have compatibility issues with modern Oracle features or column types.

How to fix: Switch to Oracle's official ODBC Driver (Oracle ODBC Driver), which is regularly updated and supports all modern Oracle data types and features. This often resolves weird edge cases like this.

5. ADO Recordset Default Settings #

When you use conn1.Execute(strquery3) directly to get a Recordset, it uses default cursor and lock types that might not play well with certain column types. Explicitly configuring your Recordset could fix the issue.

Try replacing your Recordset code with this:

Set rs3 = CreateObject("ADODB.Recordset")
rs3.CursorType = 1 ' adOpenKeyset
rs3.LockType = 3 ' adLockOptimistic
rs3.Open strquery3, conn1

This explicit configuration can resolve compatibility issues between the driver and ADO's default behavior.

内容的提问来源于stack exchange，提问作者Rajat Solanki