ORA-22816 on edit with view that has a CLOB field

[cis-wurzen]

I found another failure in ODAC 7 for something that works with ODAC 5. When editing a view that has a CLOB field then ODAC 7 thinks the CLOB field was modified and tries to update it.

The test case for this is the following:

SQL for the test table and view

Code: Select all

CREATE TABLE ODACTESTCLOBTAB(
  ID                                 NUMBER(10,0),
  FIELD1                             NUMBER(1,0),
  FIELD2                             CLOB
);

ALTER TABLE ODACTESTCLOBTAB ADD CONSTRAINT IDX_ODACTESTCLOBTAB_ID PRIMARY KEY (ID);

INSERT INTO ODACTESTCLOBTAB(ID, FIELD1)
VALUES(1, 5);

COMMIT;

CREATE OR REPLACE VIEW V_ODACTESTCLOBTAB AS
SELECT ID, FIELD1, FIELD2
FROM ODACTESTCLOBTAB
/

CREATE OR REPLACE TRIGGER TR_ODACTESTCLOBTAB_UPDATE INSTEAD OF UPDATE ON V_ODACTESTCLOBTAB
BEGIN
  UPDATE ODACTESTCLOBTAB SET
    FIELD1 = :NEW.FIELD1
  WHERE (ID = :OLD.ID);
END TR_ODACTESTCLOBTAB_UPDATE;
/

Delphi console application

Code: Select all

program ODACViewEditTest;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Ora,
  DBAccess,
  OraSmart;

{
CREATE TABLE ODACTESTCLOBTAB(
  ID                                 NUMBER(10,0),
  FIELD1                             NUMBER(1,0),
  FIELD2                             CLOB
);

ALTER TABLE ODACTESTCLOBTAB ADD CONSTRAINT IDX_ODACTESTCLOBTAB_ID PRIMARY KEY (ID);

INSERT INTO ODACTESTCLOBTAB(ID, FIELD1)
VALUES(1, 5);

COMMIT;

CREATE OR REPLACE VIEW V_ODACTESTCLOBTAB AS
SELECT ID, FIELD1, FIELD2
FROM ODACTESTCLOBTAB
/

CREATE OR REPLACE TRIGGER TR_ODACTESTCLOBTAB_UPDATE INSTEAD OF UPDATE ON V_ODACTESTCLOBTAB
BEGIN
  UPDATE ODACTESTCLOBTAB SET
    FIELD1 = :NEW.FIELD1
  WHERE (ID = :OLD.ID);
END TR_ODACTESTCLOBTAB_UPDATE;
/

}

const
  cServer = 'YourServer';
  cUsername = 'YourUser';
  cPassword = 'YourPassword';

function TestEditWithView: Boolean;
var
  se: TOraSession;
  qr: TSmartQuery;
  V: Integer;
begin
  se := TOraSession.Create(nil);
  qr := TSmartQuery.Create(nil);
  try
    se.Server := cServer;
    se.Username := cUserName;
    se.Password := cPassword;
    with qr do
    begin
      Session := se;
      SQL.Add('SELECT * FROM V_ODACTESTCLOBTAB');
      SQL.Add('WHERE');
      SQL.Add('      (ID = :ID)');
      ParamByName('ID').AsInteger := 1;
      Keyfields := 'ID';
      Options.DeferredLobRead := True;
      FetchAll := True;
      RefreshOptions := [roBeforeEdit];
      Options.DeferredLobRead := True;
      Options.CacheLobs := False;
      Options.ReturnParams := True;
      Options.LongStrings := False;
      Open;
      V := FieldByName('FIELD1').AsInteger;
      Edit;
      FieldByName('FIELD1').AsInteger := V + 1;
      Post;
      Close;
      Open;
      Result := FieldByName('FIELD1').AsInteger = V + 1;
      Close;
    end;
  finally
    qr.Free;
    se.Free;
  end;
end;

begin
  try
    if TestEditWithView then
      WriteLn('PASS')
    else
      WriteLn('FAIL');    
  except
    on E: Exception do
    begin
      WriteLn('FAIL - Exception Error');
      WriteLn('  E.ClassName = ', E.ClassName);
      WriteLn('  E.Message = ', E.Message);      
    end;
  end;
  ReadLn;
end.

Steps:
- execute the script
- create a new console application in Delphi and paste the code
- adjust the constants cServer, cUserName and cPassword
- compile and run the example

expected: output is PASS
actual:
Delphi 5 with ODAC 5.80.0.42: output is PASS
Delphi 5 with ODAC 7.10.0.4 or Delphi XE with ODAC 7.10.0.5:
output is
FAIL - Exception Error
E.ClassName = EOraError
E.Message = ORA-22816: ...

[AlexP]

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

[cis-wurzen]

Have you meanwhile found the reason and can provide me with a patch for the source?

[AlexP]

Hello,

We have already fixed the problem.
This fix will be included in the next build.
The next build will be released this week.