Bizarre Oracle 11 / ADO.NET Error

Question

I am using Oracle 11.1.0.6.0 and ADO.NET (System.Data.OracleClient) to run a query that works fine with Oracle 9 & 10 but on Oracle 11 we are getting the error:

System.Data.OracleClient.OracleException was unhandled Message="ORA-01405: fetched column value is NULL\n" Source="System.Data.OracleClient" ErrorCode=-2146232008 Code=1405

There are no nulls anywhere in this data other than those that are brought in by the optional join on the AFE_ACCRUAL_TOTALS_GROSS table.

The query only fails with this error if I run it via ADO. If I run from within TOAD it works fine. I've tried tracing the SQL being sent to Oracle just to be sure that ADO.NET is not rewriting anything but it remains untouched. No SQL runs before this query.

It is hugely data dependent. If I remove the record from the AFE table where AFEID=361 (even though it is absolutely identical to 359, 362, 363, ...) it all of a sudden starts working. Similar effect when I remove other records.

I'm running completely out of ideas here and I would appreciate some help on how to proceed on this. I assume it's some sort of bug between ADO.NET and Oracle but I'm not sure how to get any further on this. I don't think I've got this simple enough that I'll be able to report it to MSFT or ORA.

I've narrowed down my schema and data and query to a minimal example the still produces the error. Unfortunately it's still rather large. (forgive the odd query in the code sample at the bottom, it wouldn't normally do that cross join)

The schema:

CREATE TABLE AFE ( AFEID NUMBER(10) NOT NULL, USERID NUMBER(10) );
CREATE TABLE AFEPARTNER ( AFEID NUMBER(10) NOT NULL, PARTNERID NUMBER(10) NOT NULL );
CREATE TABLE AFE_ACCRUAL_TOTALS_GROSS ( AFEID NUMBER(10) NOT NULL, GROSS_ACTUAL NUMBER(27,10) DEFAULT 0 NOT NULL );
CREATE TABLE USERS ( USERID NUMBER(10), NAME VARCHAR2(50 BYTE) );

CREATE INDEX IDX_AFEPARTNER_AFEID ON AFEPARTNER (AFEID);
ALTER TABLE AFE ADD (  PRIMARY KEY (AFEID));
ALTER TABLE AFEPARTNER ADD (  PRIMARY KEY (AFEID, PARTNERID));
ALTER TABLE AFE_ACCRUAL_TOTALS_GROSS ADD (  PRIMARY KEY (AFEID));
ALTER TABLE USERS ADD (  PRIMARY KEY (USERID));
ALTER TABLE AFE ADD (  FOREIGN KEY (USERID) REFERENCES USERS (USERID));
ALTER TABLE AFEPARTNER ADD (  FOREIGN KEY (AFEID) REFERENCES AFE (AFEID) ON DELETE CASCADE);
ALTER TABLE AFE_ACCRUAL_TOTALS_GROSS ADD (  FOREIGN KEY (AFEID) REFERENCES AFE (AFEID) ON DELETE CASCADE);

and the data loaded into the table is as follows:

SET DEFINE OFF;
Insert into AFE
   (AFEID, USERID)
 Values
   (331, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (333, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (305, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (334, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (302, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (337, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (307, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (309, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (310, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (345, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (346, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (314, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (351, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (316, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (311, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (313, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (317, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (327, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (355, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (356, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (332, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (358, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (359, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (362, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (363, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (335, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (347, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (353, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (361, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (370, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (373, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (374, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (375, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (377, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (381, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (382, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (383, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (386, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (387, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (388, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (349, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (365, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (369, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (380, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (389, 1);
Insert into AFE
   (AFEID, USERID)
 Values
   (303, 1);
COMMIT;

SET DEFINE OFF;
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (347, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (353, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (361, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (373, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (332, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (380, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (311, 1231);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (313, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (302, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (314, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (316, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (305, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (303, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (331, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (334, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (335, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (337, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (327, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (345, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (346, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (349, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (351, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (355, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (356, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (359, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (363, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (369, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (370, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (374, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (377, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (389, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (310, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (309, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (307, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (358, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (362, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (365, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (375, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (317, 0);
Insert into AFE_ACCRUAL_TOTALS_GROSS
   (AFEID, GROSS_ACTUAL)
 Values
   (333, 0);
COMMIT;

SET DEFINE OFF;
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (302, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (302, 16);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (302, 20);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (303, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (303, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (303, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (305, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (305, 9);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (305, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (305, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (305, 18);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (307, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (307, 9);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (307, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (307, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (307, 18);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (309, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (309, 9);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (309, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (309, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (309, 18);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (310, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (310, 9);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (310, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (310, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (310, 18);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (311, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (311, 11);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (311, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (313, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (313, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (313, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (314, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (314, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (314, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (316, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (316, 7);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (316, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (317, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (327, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (327, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (327, 15);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (331, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (332, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (332, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (333, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (333, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (334, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (334, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (334, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (335, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (335, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (335, 22);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (337, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (337, 5);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (337, 15);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (345, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (346, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (347, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (347, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (347, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (349, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (349, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (349, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (351, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (351, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (351, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (353, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (353, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (353, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (355, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (355, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (355, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (356, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (356, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (356, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (358, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (358, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (358, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (359, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (359, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (359, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (361, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (361, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (361, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (362, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (362, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (362, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (363, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (363, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (363, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (365, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (365, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (365, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (369, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (369, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (369, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (370, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (370, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (370, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (373, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (373, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (373, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (374, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (374, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (374, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (375, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (375, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (375, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (377, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (377, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (377, 14);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (377, 19);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (380, 1);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (381, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (381, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (382, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (382, 17);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (383, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (383, 10);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (386, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (387, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (388, 2);
Insert into AFEPARTNER
   (AFEID, PARTNERID)
 Values
   (389, 1);
COMMIT;

SET DEFINE OFF;
Insert into USERS
   (USERID, NAME)
 Values
   (26, 'Test');
Insert into USERS
   (USERID, NAME)
 Values
   (27, 'Group Admin User');
Insert into USERS
   (USERID, NAME)
 Values
   (2, 'Originator1');
Insert into USERS
   (USERID, NAME)
 Values
   (3, 'Originator2');
Insert into USERS
   (USERID, NAME)
 Values
   (4, 'Originator3');
Insert into USERS
   (USERID, NAME)
 Values
   (5, 'Originator4');
Insert into USERS
   (USERID, NAME)
 Values
   (6, 'Originator5');
Insert into USERS
   (USERID, NAME)
 Values
   (7, 'User1');
Insert into USERS
   (USERID, NAME)
 Values
   (8, 'User2');
Insert into USERS
   (USERID, NAME)
 Values
   (9, 'User3');
Insert into USERS
   (USERID, NAME)
 Values
   (10, 'User4');
Insert into USERS
   (USERID, NAME)
 Values
   (11, 'User5');
Insert into USERS
   (USERID, NAME)
 Values
   (12, 'Reviewer1');
Insert into USERS
   (USERID, NAME)
 Values
   (13, 'Reviewer2');
Insert into USERS
   (USERID, NAME)
 Values
   (14, 'Reviewer3');
Insert into USERS
   (USERID, NAME)
 Values
   (15, 'Reviewer4');
Insert into USERS
   (USERID, NAME)
 Values
   (16, 'Reviewer5');
Insert into USERS
   (USERID, NAME)
 Values
   (17, 'CEO');
Insert into USERS
   (USERID, NAME)
 Values
   (18, 'CFO');
Insert into USERS
   (USERID, NAME)
 Values
   (19, 'VP Finance');
Insert into USERS
   (USERID, NAME)
 Values
   (20, 'VP Exploration');
Insert into USERS
   (USERID, NAME)
 Values
   (21, 'VP Operation');
Insert into USERS
   (USERID, NAME)
 Values
   (22, 'VP Land');
Insert into USERS
   (USERID, NAME)
 Values
   (23, 'Production Manager');
Insert into USERS
   (USERID, NAME)
 Values
   (24, 'Land Manager');
Insert into USERS
   (USERID, NAME)
 Values
   (25, 'Exploration Manager');
Insert into USERS
   (USERID, NAME)
 Values
   (1, 'System Admin');
Insert into USERS
   (USERID, NAME)
 Values
   (28, 'Joan');
Insert into USERS
   (USERID, NAME)
 Values
   (29, 'VMM');
COMMIT;

The test code:

    OracleConnection connection = new OracleConnection();
    connection.ConnectionString = "Data Source=test11g;User Id=afe_jeff2;Password=afe_jeff2";
    connection.Open();


    string SQL =
          "SELECT  gross_actual" +
          "  FROM afe, " +
          "       afepartner, " +
          "       users, " +
          "       afe_accrual_totals_gross " +
          "        " +
          " WHERE " +
          "         afe.userid = users.userid" +
          "       and afe.afeid = afe_accrual_totals_gross.afeid(+)"; 

        IDbCommand cmd = connection.CreateCommand();
        cmd.CommandText = SQL;
        IDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())  // EXCEPTION IS RAISED ON THIS CALL
        {
        }
        rdr.Close();

Answer 1

Hi Jeff, have you tried using another ADO.NET provider to see if you can narrow it down to the MS one? Oh, also note MS have recently anounced that they are not going to continue with their Oracle provider, maybe its a good time to look around for a new one anyway.

DevArt (previously CoreLabs) have the most commonly used 3rd party provider.

FYI Toad for Oracle is written in Delphi and talks directly to the Oracle Call Interface (OCI) so its not totally surprising (not good, mind you) that you would see different behaviour.

Answer 2

As a work around, have you tried a view over the query or a SELECT CAST(gross_actual AS NUMBER) gross_actual

If it is a bug in the provider, it may be confused by the fact that the column is not null on the database but can return a null in the query because of the outer-join.

PS. Don't know whether you've simplified the query before putting it up, but what is afepartner doing in the FROM without any join to another table ?