Hello!
I am getting "Unspecified Error" messages when using OLE DB Provider. This happens only for a few tables of a database.
It only happens in more recent versions of the Oracle Ole DB Provider: 21.7, 21.4, 21.3, 19.16 and 19.15.1. It does NOT happen with earlier versions like 19.3, 18.3 and 12.2.0.1.
It happens in either 32-Bit as well as 64-Bit client versions.
It does only happen when using a client-side cursor. Server-side cursors are fine.
Permission on the Oracle client program directory is set to "Full Control" for "Everyone".
Steps to reproduce:
1. Create the following table:
CREATE TABLE TESTTABLE (
F1 NVARCHAR2(33) NOT NULL,
F2 NVARCHAR2(4),
F NVARCHAR2(1),
F4 NVARCHAR2(2),
F5 NVARCHAR2(50),
F6 NVARCHAR2(33),
F7 NVARCHAR2(12),
F8 NVARCHAR2(12),
F9 NVARCHAR2(10),
F10 NUMBER(5,0),
F11 NVARCHAR2(100),
F12 NVARCHAR2(255),
F13 NVARCHAR2(100),
F14 NUMBER(5,0),
F15 NUMBER(1,0),
F16 NUMBER(1,0),
F17 NVARCHAR2(30),
F18 NUMBER(5,0),
F19 NUMBER(5,0),
F20 NUMBER(5,0),
F21 NUMBER(5,0),
F22 NVARCHAR2(20),
F23 NVARCHAR2(20),
F24 NUMBER(5,0),
F25 NUMBER(9,0),
F26 NVARCHAR2(30),
F27 NVARCHAR2(100),
F28 NUMBER(9,0),
F29 NVARCHAR2(151),
F30 NUMBER(9,0),
F31 NVARCHAR2(33),
F32 NVARCHAR2(30),
F33 NUMBER(9,0),
F34 NVARCHAR2(50),
F35 NUMBER(9,0),
F36 NUMBER(9,0),
F37 NUMBER(9,0),
F38 NUMBER(9,0),
F39 NVARCHAR2(100),
F40 NUMBER(9,0),
F41 NVARCHAR2(150),
F42 NUMBER(9,0),
F43 NUMBER(9,0),
F44 NVARCHAR2(33),
F45 NVARCHAR2(33),
F46 NVARCHAR2(33),
F47 NCLOB,
F48 NVARCHAR2(33),
F49 NVARCHAR2(42),
F50 NVARCHAR2(33),
F51 NVARCHAR2(10),
F52 NUMBER(9,0),
F53 NVARCHAR2(250),
F54 NVARCHAR2(50),
F55 NVARCHAR2(15),
F56 NVARCHAR2(255),
PRIMARY KEY (F1)
);
2. Insert some records:
BEGIN
FOR V_COUNTER IN 1..2000
LOOP
INSERT INTO TESTTABLE (F1) VALUES(to_nchar(V_COUNTER));
END LOOP;
END;
3. Select records from the table using OLE DB.
For example you may use the following VBA macro:
Sub Button1_Click()
On Error GoTo ErrorHandler
Dim Connection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Set Connection = New ADODB.Connection
Connection.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=true;User ID=MYUSER;Data Source=MYEZCONNECTSTRING;Password=MYPASSWORD"
Set RecordSet = New ADODB.RecordSet
RecordSet.CursorLocation = adUseClient
RecordSet.Open "select * from TESTTABLE", Connection, adOpenKeyset, adLockOptimistic
MsgBox RecordSet.RecordCount & " records found"
Connection.Close
Exit Sub
ErrorHandler:
MsgBox "ERROR: " & Err.Description
Connection.Close
End Sub
- When selecting all records
SELECT * FROM TESTTABLE;
it results in an "Unspecified Error".
- When reducing the number of selected records
select * from TESTTABLE where ROWNUM < 200
the error turns into a "Data provider or other service returned an E_FAIL status."
- When reducing the amount even more
select * from TESTTABLE where ROWNUM < 100
the records are returned successfully.
What could be wrong here?