All geek questions in one place

Bizarre Oracle Error 11 / ADO.NET

I am using Oracle 11.1.0.6.0 and ADO.NET (System.Data.OracleClient) to execute a query that works fine with Oracle 9 and 10, but in Oracle 11 we get an error:

System.Data.OracleClient.OracleException was not processed Message = "ORA-01405: column value assigned to the column is NULL \ n" Source = "System.Data.OracleClient" ErrorCode = -2146232008 Code = 1405

There are no zeros in this data, except for those that were added to the optional join in the AFE_ACCRUAL_TOTALS_GROSS table.

The request only runs with this error if I run it through ADO. If I run TOAD from inside, it works fine. I tried to track the SQL submission to Oracle just to make sure that ADO.NET was not rewriting anything but was left untouched. Before this query, SQL is not executed.

It is extremely data dependent. If I delete an entry from the AFE table, where AFEID = 361 (even if it is absolutely identical to 359, 362, 363, ...), it suddenly starts working. A similar effect when deleting other entries.

I am running completely out of ideas here, and I would appreciate some help on how to continue this. I assume this is some kind of error between ADO.NET and Oracle, but I am not sure how to do this further. I don’t think it’s simple enough that I can report it to MSFT or ORA.

, . , . ( , )

:

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);

, , :

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;

:

    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();
+3
3

ADO.NET, , MS? , , MS , Oracle, , , .

DevArt ( CoreLabs) .

FYI Toad Oracle Delphi Oracle Call Interface (OCI), ( , ), .

+2

10.1 . , OracleClient Oracle 11 .

+1

, SELECT CAST (gross_actual AS NUMBER) gross_actual

, , , - .

PS. , , afepartner FROM - ?

0

Source: https://habr.com/ru/post/1711769/

More articles:


All Articles