ORA-01400 cannot insert NULL with SUBSELECT

cis-wurzen · Post by **cis-wurzen** » Thu 21 Jul 2011 08:33

Delphi 5, ODAC 7.20.0.7
Delphi XE, ODAC 7.20.0.7

Steps to reproduce

1) Create new app

2) Drop button on form

3) Insert code into OnClick-Event

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  Boo: array[Boolean] of string = ('False', 'True');
var
  SE: TOraSession;
  QR: TSmartQuery;
  ID: TField;
begin
  SE := TOraSession.Create(Self);
  SE.ConnectString := 'DEMO/DEMO@ORCL';
  SE.Connect;

  SE.ExecSQL('CREATE TABLE ODACTEST(ID NUMBER(16) NOT NULL, C1 VARCHAR2(1))', []);
  SE.ExecSQL('ALTER TABLE ODACTEST ADD CONSTRAINT PK_ODACTEST PRIMARY KEY(ID)', []);

  QR := TSmartQuery.Create(Self);
  QR.Session := SE;
  QR.SQL.Add('SELECT DISTINCT S.ID,S.C1 FROM');
  QR.SQL.Add('(');
  QR.SQL.Add('SELECT * FROM ODACTEST WHERE C1 = ''A''');
  QR.SQL.Add(') S');

  QR.KeyFields := 'ID';
  QR.UpdatingTable := 'ODACTEST';

  QR.Open;
  ID := QR.FieldbyName('ID');

  ShowMessage(DACVersion);
  ShowMessage('Required = ' + Boo[ID.Required]); // -> True, OK because NOT NULL
  ShowMessage('ReadOnly = ' + Boo[ID.ReadOnly]); // -> True in 7.20 - False in 5.80 - Why?

  ID.ReadOnly := False;

  QR.Append;
  ID.AsInteger := GetTickCount;
  QR.Post;

  {
  Working in 5.80
  In ODAC 7.20 this results in Exception:
  ORA-01400 cannot insert NULL into ("DEMO"."ODACTEST"."ID")
  because this Statement is sent:
  INSERT INTO ODACTEST
    (C1)
  VALUES
    (:C1)
  }
end;

4) Run (F9)

5) Click Button

AlexP · Post by **AlexP** » Thu 21 Jul 2011 09:55

Hello,

I cannot reproduce the problem.

Your code works correctly in ODAC 7.20.0.7 and 7.20.0.8 - the ID.ReadOnly field is false and the text of the insert query is formed correctly:

Code: Select all

INSERT INTO ODACTEST
  (ID)
VALUES
 (:ID)

Please specify the version of Oracle server and client you are using and check that you have deleted all ODAC files from the previous version (dcu, bpl, pas, etc.)

cis-wurzen · Post by **cis-wurzen** » Thu 21 Jul 2011 10:28

Just downloaded and installed 7.20.0.8 and now it works as expected.

BTW: DACVersion still reports 7.10.0.8 (same as in .6 and .7)

AlexP · Post by **AlexP** » Thu 21 Jul 2011 11:27

Hello,

The DACVersion constant shows the version of the core that is shared by all components. If DACVersion shows the same version for several ODAC versions, it's likely that Library Path points to an old version of ODAC. If you need to obtain the version of the exact product, for example, of ODAC, you should use the OdacVersion constant:

Code: Select all

ShowMessage(OdacVersion);

cis-wurzen · Post by **cis-wurzen** » Fri 22 Jul 2011 07:49

I went from ODACversion to DACversion in v4.50.something because ODACversion was wrong.

Switched now back to ODACversion.

Thanks. Could be closed.

AlexP · Post by **AlexP** » Fri 22 Jul 2011 08:32

Hello,

If you have any other questions, feel free to contact us.