Devart Forums

Posted: **Mon 27 Feb 2017 13:23**

Hi Team,

We have a problem with TOraArray containing TOraObjects. This is a 32-bit VCL problem only. In 64-bit VCL project the example code below works perfectly.

Oracle SQL code:

Code: Select all

CREATE OR REPLACE TYPE TP_TEST AS OBJECT (
    SZAM            NUMBER(9),
    SZOVEG          VARCHAR2(40),
    DATUM           DATE,
    BDOUBLE         BINARY_DOUBLE
) FINAL;
/

CREATE TABLE T_TEST OF TP_TEST;

INSERT INTO T_TEST (SZAM, SZOVEG, DATUM, BDOUBLE) VALUES (1, 'One', SYSDATE + 1, 1);
INSERT INTO T_TEST (SZAM, SZOVEG, DATUM, BDOUBLE) VALUES (2, 'Two', SYSDATE + 2, 2);
INSERT INTO T_TEST (SZAM, SZOVEG, DATUM, BDOUBLE) VALUES (3, 'Three', SYSDATE + 3, 3);

COMMIT;

CREATE OR REPLACE TYPE TP_TEST_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_TEST,
    CONSTRUCTOR FUNCTION TP_TEST_EXT RETURN SELF AS RESULT
) FINAL;
/

CREATE OR REPLACE TYPE BODY TP_TEST_EXT
AS
    CONSTRUCTOR FUNCTION TP_TEST_EXT RETURN SELF AS RESULT
    AS
    BEGIN
        EXTSTATUS := 0;
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE TYPE TP_TEST_EXT_TBL AS TABLE OF TP_TEST_EXT;
/

CREATE OR REPLACE PROCEDURE P_GET_TEST_EXT_TBL (
    oLISTA              OUT             TP_TEST_EXT_TBL
) AS
BEGIN
    
    oLISTA := TP_TEST_EXT_TBL();
    
    FOR r IN (SELECT t.OBJECT_VALUE ov FROM T_TEST t)
    LOOP
        oLISTA.EXTEND;
        oLISTA(oLISTA.LAST) := TP_TEST_EXT();
        oLISTA(oLISTA.LAST).REC := r.OV;
    END LOOP;
    
END P_GET_TEST_EXT_TBL;
/

Delphi code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  OSP: TOraStoredProc;
  R: TOraArray;
begin
  OraSession.Connect;

  OSP := TOraStoredProc.Create(nil);
  R := TOraArray.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_TEST_EXT_TBL'));
  try
    OSP.Session := OraSession;
    OSP.AutoCommit := True;
    OSP.StoredProcName := 'P_GET_TEST_EXT_TBL';
    OSP.ParamCheck := True;
    OSP.Prepare;
    OSP.ParamByName('oLISTA').DataType := ftObject;
    OSP.ParamByName('oLISTA').ParamType := ptOutput;
    OSP.ParamByName('oLISTA').AsObject := R;
    OSP.Execute;

    // OCI-22060 error in 32-bit VCL project (works perfectly on 64-bit)
    ShowMessage(IntToStr(R.ItemAsObject[0].AttrAsObject['REC'].AttrAsInteger['SZAM']));
  finally
    OSP.Free;
    R.Free;
  end;
end;

Note: If the Oracle object types above are declared as NOT FINAL then the error is not occurs in 32-bit VCL!

Best regards,

Balázs Miereisz
WINFORM Kft.

Posted: **Mon 27 Feb 2017 14:53**

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

Posted: **Thu 06 Apr 2017 12:25**

Hi Team,

We need your help again! We have downloaded and installed ODAC VCL 10.0.1 for RAD Studio 10.1 Berlin. The problem is solved for example object declared as FINAL

but declared as NOT FINAL object we get the same access violation error as before

!

This is only a 32-bit VCL problem. On 64-bit it works perfect.

Best regards,

Balázs Miereisz
WINFORM Kft.

Posted: **Fri 07 Apr 2017 09:59**

Hi Team,

Here I post another example for this problem for debugging.

SQL types:

Code: Select all

-- Head object type
CREATE OR REPLACE TYPE TP_HEAD AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGEN IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Head sequence
CREATE SEQUENCE S_HEAD;

-- Head object type body
CREATE OR REPLACE TYPE BODY TP_HEAD
AS
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGEN IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGEN = 'T'
        THEN
            ID := S_HEAD.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Head table
CREATE TABLE T_HEAD OF TP_HEAD (CONSTRAINT HEAD_PK PRIMARY KEY (ID));

INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'First', 1, 1);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Second', 2, 2);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Third', 3, 3);
COMMIT;

-- Item object type
CREATE OR REPLACE TYPE TP_ITEM AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    HEADID          NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGEN IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Item sequence
CREATE SEQUENCE S_ITEM;

-- Item object type body
CREATE OR REPLACE TYPE BODY TP_ITEM
AS
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGEN IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGEN = 'T'
        THEN
            ID := S_ITEM.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Item table
CREATE TABLE T_ITEM OF TP_ITEM (CONSTRAINT ITEM_PK PRIMARY KEY (ID));

ALTER TABLE T_ITEM ADD CONSTRAINT ITEM_HEADID_FK FOREIGN KEY (HEADID) REFERENCES T_HEAD (ID);

INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.1', 1, 1, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.2', 2, 2, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '2.1', 3, 3, (SELECT ID FROM T_HEAD WHERE VCH = 'Second'));
COMMIT;

-- Item ext
CREATE OR REPLACE TYPE TP_ITEM_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_ITEM,
    
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGEN IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_ITEM_EXT
AS
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGEN IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        IF iID IS NULL
        THEN
    
            REC := TP_ITEM(iIDGEN);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_ITEM t WHERE t.ID = iID;
            
            EXTSTATUS := 0;
            
        END IF;
       
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE TYPE TP_ITEM_EXT_TBL AS TABLE OF TP_ITEM_EXT;
/

-- Head ext
CREATE OR REPLACE TYPE TP_HEAD_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_HEAD,
    
    ITEMLIST                TP_ITEM_EXT_TBL,
    
    -- Saját konstruktorok
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGEN IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_HEAD_EXT
AS
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGEN IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        ITEMLIST := TP_ITEM_EXT_TBL();
        IF iID IS NULL
        THEN
    
            REC := TP_HEAD(iIDGEN);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_HEAD t WHERE t.ID = iID;
            
            FOR r IN (SELECT ID FROM T_ITEM WHERE HEADID = iID)
            LOOP
                
                ITEMLIST.EXTEND;
                ITEMLIST(ITEMLIST.LAST) := TP_ITEM_EXT('F', r.ID);
            
            END LOOP;
            
            EXTSTATUS := 0;
            
        END IF;
        
        EXTRESULT := '';

        RETURN;
    END;
END;
/

Delphi code:

Code: Select all

procedure Test1
var
  OSQL: TOraSQL;
  Rec: TOraObject;
begin
  OraSession.Connect;
  
  Rec := TOraObject.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_HEAD_EXT'));
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add(':RESULT := NEW TP_HEAD_EXT(''T'');');
    OSQL.SQL.Add('END;');
    OSQL.ParamByName('RESULT').AsObject := Rec;
    OSQL.Execute;

    // Here Rec.AttrAsObject['REC'] is inaccessible! (access violation)
  finally
    OSQL.Free;
  end;
end;

procedure Test2
var
  OSQL: TOraSQL;
  Rec: TOraObject;
begin
  OraSession.Connect;
  
  Rec := TOraObject.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_ITEM_EXT'));
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add(':RESULT := NEW TP_ITEM_EXT(''T'');');
    OSQL.SQL.Add('END;');
    OSQL.ParamByName('RESULT').AsObject := Rec;
    OSQL.Execute;

    // Here Rec.AttrAsObject['REC'].AttrAsInteger['ID']; is inaccessible! (access violation)
  finally
    OSQL.Free;
  end;
end;

Posted: **Sat 13 May 2017 13:19**

Thank you for the information. The described bug was fixed. The fix will be included in the next ODAC build, the release of which is planned next week.

Posted: **Wed 17 May 2017 10:18**

Hi Team,

Thank you for the new version! The access violation problem has solved. But we have another problem with the latest version ODAC VCL (10.0.2).

This problem exists in Delphi 32 and 64 bit VCL projects.

SQL code (same as before with two new procedures):

Code: Select all

-- Head object type
CREATE OR REPLACE TYPE TP_HEAD AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Head sequence
CREATE SEQUENCE S_HEAD;

-- Head object type body
CREATE OR REPLACE TYPE BODY TP_HEAD
AS
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGENERALAS = 'T'
        THEN
            ID := S_HEAD.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Head table
CREATE TABLE T_HEAD OF TP_HEAD (CONSTRAINT HEAD_PK PRIMARY KEY (ID));

INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'First', 1, 1);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Second', 2, 2);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Third', 3, 3);
COMMIT;

-- Item object type
CREATE OR REPLACE TYPE TP_ITEM AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    HEADID          NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Item sequence
CREATE SEQUENCE S_ITEM;

-- Item object type body
CREATE OR REPLACE TYPE BODY TP_ITEM
AS
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGENERALAS = 'T'
        THEN
            ID := S_ITEM.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Item table
CREATE TABLE T_ITEM OF TP_ITEM (CONSTRAINT ITEM_PK PRIMARY KEY (ID));

ALTER TABLE T_ITEM ADD CONSTRAINT ITEM_HEADID_FK FOREIGN KEY (HEADID) REFERENCES T_HEAD (ID);

INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.1', 1, 1, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.2', 2, 2, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '2.1', 3, 3, (SELECT ID FROM T_HEAD WHERE VCH = 'Second'));
COMMIT;

-- Item ext
CREATE OR REPLACE TYPE TP_ITEM_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_ITEM,
    
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_ITEM_EXT
AS
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        IF iID IS NULL
        THEN
    
            REC := TP_ITEM(iIDGENERALAS);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_ITEM t WHERE t.ID = iID;
            
            EXTSTATUS := 0;
            
        END IF;
       
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE TYPE TP_ITEM_EXT_TBL AS TABLE OF TP_ITEM_EXT;
/

-- Head ext
CREATE OR REPLACE TYPE TP_HEAD_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_HEAD,
    
    ITEMLIST                TP_ITEM_EXT_TBL,
    
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_HEAD_EXT
AS
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        ITEMLIST := TP_ITEM_EXT_TBL();
        IF iID IS NULL
        THEN
    
            REC := TP_HEAD(iIDGENERALAS);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_HEAD t WHERE t.ID = iID;
            
            FOR r IN (SELECT ID FROM T_ITEM WHERE HEADID = iID)
            LOOP
                
                ITEMLIST.EXTEND;
                ITEMLIST(ITEMLIST.LAST) := TP_ITEM_EXT('F', r.ID);
            
            END LOOP;
            
            EXTSTATUS := 0;
            
        END IF;
        
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE PROCEDURE P_GET_HEAD_EXT (
    iID                 IN              NUMBER,
    oEXT                OUT             TP_HEAD_EXT
) AS
BEGIN
    
    oEXT := TP_HEAD_EXT('F', iID);
    
END P_GET_HEAD_EXT;
/

CREATE OR REPLACE PROCEDURE P_HEAD_EXT (
    iEXT                IN              TP_HEAD_EXT
) AS
BEGIN
    IF iEXT.ITEMLIST IS NOT NULL AND iEXT.ITEMLIST.COUNT > 0
    THEN
        FOR r IN (iEXT.ITEMLIST.FIRST..iEXT.ITEMLIST.LAST)
        LOOP
            IF iEXT.ITEMLIST(r).REC IS NULL
            THEN
                RAISE_APPLICATION_ERROR (-20100, 'Itemlist item object is null. Item index: ' || TO_CHAR(r));
            END IF;
        END LOOP;
    END IF;
END P_HEAD_EXT;
/

Delphi code:

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, ssAdvCtrls, Ora, OraObjects, Data.DB,
  OraCall, DBAccess, Vcl.StdCtrls, ssStdCtrls;

type
  TForm1 = class(TForm)
    OraSession: TOraSession;
    Edit1: TEdit;
    btnReadData: TButton;
    btnWriteData: TButton;
    procedure btnReadDataClick(Sender: TObject);
    procedure btnWriteDataClick(Sender: TObject);
  private
    Rec: TOraObject;
    ID: Integer;
    HeadID: Integer;
    procedure CreateRecObject;
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.CreateRecObject;
var
  OSQL: TOraSQL;
begin
  OraSession.Connect;
  Rec := TOraObject.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_HEAD_EXT'));
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add(':RESULT := NEW TP_HEAD_EXT(''F'');');
    OSQL.SQL.Add('END;');
    OSQL.ParamByName('RESULT').AsObject := Rec;
    OSQL.Execute;
  finally
    OSQL.Free;
  end;
end;

procedure TForm1.btnReadDataClick(Sender: TObject);
var OSP: TOraStoredProc;
begin
  CreateRecObject;
  OSP := TOraStoredProc.Create(nil);
  try
      OSP.Session := OraSession;
      OSP.AutoCommit := True;
      OSP.StoredProcName := 'P_GET_HEAD_EXT';
      OSP.ParamCheck := True;
      OSP.Prepare;
      OSP.ParamByName('iID').DataType := ftInteger;
      OSP.ParamByName('iID').ParamType := ptInput;
      OSP.ParamByName('iID').AsInteger := 1;
      OSP.ParamByName('oEXT').DataType := ftObject;
      OSP.ParamByName('oEXT').ParamType := ptOutput;
      OSP.ParamByName('oEXT').AsObject := Rec;
      OSP.Execute;
    finally
      OSP.Free;
    end;
  Edit1.Text := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsString['VCH'];
  ID := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsInteger['ID'];
  HeadID := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsInteger['HEADID'];
end;

procedure TForm1.btnWriteDataClick(Sender: TObject);
var
  OSQL: TOraSQL;
  K: Integer;
begin
  K := Rec.AttrAsArray['ITEMLIST'].AppendItem;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsInteger['ID'] := ID+100;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsInteger['HEADID'] := HeadID;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsString['VCH'] := Edit1.Text;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsInteger['EXTSTATUS'] := 2;
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.AutoCommit := True;
    OSQL.ParamCheck := True;

    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add('P_HEAD_EXT(:iEXT);');
    OSQL.SQL.Add('END;');

    OSQL.ParamByName('iEXT').DataType := ftObject;
    OSQL.ParamByName('iEXT').ParamType := ptInputOutput;
    OSQL.ParamByName('iEXT').AsObject := Rec;

    // Here TP_HEAD_EXT.ITEMLIST(3).REC object has value (NOT NULL)
    ShowMessage('Item ' + IntToStr(K + 1) + ': ' + Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsString['VCH']);

    OSQL.Execute; // In P_HEAD_EXT procedure ITEMLIST(3).REC IS NULL!
  finally
    OSQL.Free;
  end;
end;

end.

1. Procedure btnReadDataClick reads the head object (ID = 1) with two item objects.
2. Procedure btnWriteDataClick adds a new item element (TP_ITEM_EXT type) then calls P_HEAD_EXT to write data to the database.

On Delphi side the new element exists but in Oracle side (in the procedure) TP_ITEM_EXT.REC has NULL value!

This code has worked before with ODAC 9.x version.

Unfortunately ODAC VCL 10.x works only with Delphi 10.2 and we cannot upgrade our project to Delphi 10.2 until this problem exists.

Best regards,

Balázs Miereisz
WINFORM Kft.

Posted: **Thu 18 May 2017 12:52**

Thank you for the information. We started to investigate the problem with creating database objects used in your code. We have successfully executed the sent DDL script, but we failed to create the P_HEAD_EXT procedure due to the present in the script text syntax error. For further investigation, please provide the correct DDL-script for creating the P_HEAD_EXT procedure

Posted: **Thu 18 May 2017 13:52**

Hi Team,

P_HEAD_EXT procedure:

Code: Select all

CREATE OR REPLACE PROCEDURE P_HEAD_EXT (
    iEXT                IN              TP_HEAD_EXT
) AS
BEGIN
    IF iEXT.ITEMLIST IS NOT NULL AND iEXT.ITEMLIST.COUNT > 0
    THEN
        FOR r IN iEXT.ITEMLIST.FIRST..iEXT.ITEMLIST.LAST
        LOOP
            IF iEXT.ITEMLIST(r).REC IS NULL
            THEN
                RAISE_APPLICATION_ERROR (-20100, 'Itemlist item object is null. Item index: ' || TO_CHAR(r));
            END IF;
        END LOOP;
    END IF;
END P_HEAD_EXT;
/

Best regards,
Balázs Miereisz

Posted: **Wed 24 May 2017 08:50**

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

Posted: **Tue 30 May 2017 07:58**

We investigated the problem and fixed it. The fix will be included in the next ODAC build. We constantly test our products, including the work with Oracle object data types.
However, there has never been any sample similar to the used in your project. We added this sample for testing our products in the future. In turn, you can also check the made
changes in your environment using ODAC night build. To get this build, please provide your license number and the used IDE using the e-support form
( https://www.devart.com - "Support»\«Request Support" menu).

Posted: **Tue 30 May 2017 14:38**

Hi Team,

We have downloaded the night build and the above problem is gone!

Thank you for the fix!

But something is still not works well... It is connected with the NULL values of some field types. In this example we have set null a DATE type column in the example table.

The error can be reproduced on both VCL 32 and 64 bit.

SQL code (same as before with an update statement on at the end):

Code: Select all

-- Head object type
CREATE OR REPLACE TYPE TP_HEAD AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Head sequence
CREATE SEQUENCE S_HEAD;

-- Head object type body
CREATE OR REPLACE TYPE BODY TP_HEAD
AS
    CONSTRUCTOR FUNCTION TP_HEAD (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGENERALAS = 'T'
        THEN
            ID := S_HEAD.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Head table
CREATE TABLE T_HEAD OF TP_HEAD (CONSTRAINT HEAD_PK PRIMARY KEY (ID));

INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'First', 1, 1);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Second', 2, 2);
INSERT INTO T_HEAD (ID, CREDATE, VCH, BDOUBLE, INTEG) VALUES (S_HEAD.NEXTVAL, SYSDATE, 'Third', 3, 3);
COMMIT;

-- Item object type
CREATE OR REPLACE TYPE TP_ITEM AS OBJECT (
    ID              NUMBER(9),
    CREDATE         DATE,
    VCH             VARCHAR2(40),
    BDOUBLE         BINARY_DOUBLE,
    INTEG           NUMBER(9),
    HEADID          NUMBER(9),
    
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
) NOT FINAL;
/

-- Item sequence
CREATE SEQUENCE S_ITEM;

-- Item object type body
CREATE OR REPLACE TYPE BODY TP_ITEM
AS
    CONSTRUCTOR FUNCTION TP_ITEM (iIDGENERALAS IN CHAR DEFAULT 'F') RETURN SELF AS RESULT
    AS
    BEGIN
        -- ID generálás
        IF iIDGENERALAS = 'T'
        THEN
            ID := S_ITEM.NEXTVAL;
        END IF;

        CREDATE := SYSDATE;

        RETURN;
    END;
END;
/

-- Item table
CREATE TABLE T_ITEM OF TP_ITEM (CONSTRAINT ITEM_PK PRIMARY KEY (ID));

ALTER TABLE T_ITEM ADD CONSTRAINT ITEM_HEADID_FK FOREIGN KEY (HEADID) REFERENCES T_HEAD (ID);

INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.1', 1, 1, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '1.2', 2, 2, (SELECT ID FROM T_HEAD WHERE VCH = 'First'));
INSERT INTO T_ITEM (ID, CREDATE, VCH, BDOUBLE, INTEG, HEADID) VALUES (S_ITEM.NEXTVAL, SYSDATE, '2.1', 3, 3, (SELECT ID FROM T_HEAD WHERE VCH = 'Second'));
COMMIT;

-- Item ext
CREATE OR REPLACE TYPE TP_ITEM_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_ITEM,
    
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_ITEM_EXT
AS
    CONSTRUCTOR FUNCTION TP_ITEM_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        IF iID IS NULL
        THEN
    
            REC := TP_ITEM(iIDGENERALAS);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_ITEM t WHERE t.ID = iID;
            
            EXTSTATUS := 0;
            
        END IF;
       
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE TYPE TP_ITEM_EXT_TBL AS TABLE OF TP_ITEM_EXT;
/

-- Head ext
CREATE OR REPLACE TYPE TP_HEAD_EXT AS OBJECT (
    EXTSTATUS               NUMBER(9),
    EXTRESULT               VARCHAR2(2000),
    REC                     TP_HEAD,
    
    ITEMLIST                TP_ITEM_EXT_TBL,
    
    -- Saját konstruktorok
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY TP_HEAD_EXT
AS
    CONSTRUCTOR FUNCTION TP_HEAD_EXT (iIDGENERALAS IN CHAR DEFAULT 'F', iID IN NUMBER DEFAULT NULL) RETURN SELF AS RESULT
    AS
    BEGIN
        ITEMLIST := TP_ITEM_EXT_TBL();
        IF iID IS NULL
        THEN
    
            REC := TP_HEAD(iIDGENERALAS);
            
            EXTSTATUS := 1;
            
        ELSE
            
            SELECT t.OBJECT_VALUE INTO REC FROM T_HEAD t WHERE t.ID = iID;
            
            FOR r IN (SELECT ID FROM T_ITEM WHERE HEADID = iID)
            LOOP
                
                ITEMLIST.EXTEND;
                ITEMLIST(ITEMLIST.LAST) := TP_ITEM_EXT('F', r.ID);
            
            END LOOP;
            
            EXTSTATUS := 0;
            
        END IF;
        
        EXTRESULT := '';

        RETURN;
    END;
END;
/

CREATE OR REPLACE PROCEDURE P_GET_HEAD_EXT (
    iID                 IN              NUMBER,
    oEXT                OUT             TP_HEAD_EXT
) AS
BEGIN
    
    oEXT := TP_HEAD_EXT('F', iID);
    
END P_GET_HEAD_EXT;
/

CREATE OR REPLACE PROCEDURE P_HEAD_EXT (
    iEXT                IN              TP_HEAD_EXT
) AS
BEGIN
    IF iEXT.ITEMLIST IS NOT NULL AND iEXT.ITEMLIST.COUNT > 0
    THEN
        FOR r IN iEXT.ITEMLIST.FIRST..iEXT.ITEMLIST.LAST
        LOOP
            IF iEXT.ITEMLIST(r).REC IS NULL
            THEN
                RAISE_APPLICATION_ERROR (-20100, 'Itemlist item object is null. Item index: ' || TO_CHAR(r));
            END IF;
        END LOOP;
    END IF;
END P_HEAD_EXT;
/

UPDATE T_ITEM SET CREDATE = NULL WHERE ID = 1;
COMMIT;

Delphi code:

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, ssAdvCtrls, Ora, OraObjects, Data.DB,
  OraCall, DBAccess, Vcl.StdCtrls, ssStdCtrls;

type
  TForm1 = class(TForm)
    OraSession: TOraSession;
    Edit1: TEdit;
    btnReadData: TButton;
    btnWriteData: TButton;
    procedure btnReadDataClick(Sender: TObject);
    procedure btnWriteDataClick(Sender: TObject);
  private
    Rec: TOraObject;
    ID: Integer;
    HeadID: Integer;
    IntValue: Integer;
    dateItem: TDateTime;
    dateRecItem: TDateTime;
    rItem: TOraObject;
    procedure CreateRecObject;
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.CreateRecObject;
var
  OSQL: TOraSQL;
begin
  OraSession.Connect;
  Rec := TOraObject.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_HEAD_EXT'));
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add(':RESULT := NEW TP_HEAD_EXT(''F'');');
    OSQL.SQL.Add('END;');
    OSQL.ParamByName('RESULT').AsObject := Rec;
    OSQL.Execute;
  finally
    OSQL.Free;
  end;

  rItem := TOraObject.Create(TOraType.Create(OraSession.OCISvcCtx, 'TP_ITEM'));
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add(':RESULT := NEW TP_ITEM(''F'');');
    OSQL.SQL.Add('END;');
    OSQL.ParamByName('RESULT').AsObject := rItem;
    OSQL.Execute;
  finally
    OSQL.Free;
  end;

end;

procedure TForm1.btnReadDataClick(Sender: TObject);
var OSP: TOraStoredProc;
  OSQL: TOraSQL;
begin
  CreateRecObject;
  OSP := TOraStoredProc.Create(nil);
  try
      OSP.Session := OraSession;
      OSP.AutoCommit := True;
      OSP.StoredProcName := 'P_GET_HEAD_EXT';
      OSP.ParamCheck := True;
      OSP.Prepare;
      OSP.ParamByName('iID').DataType := ftInteger;
      OSP.ParamByName('iID').ParamType := ptInput;
      OSP.ParamByName('iID').AsInteger := 1;
      OSP.ParamByName('oEXT').DataType := ftObject;
      OSP.ParamByName('oEXT').ParamType := ptOutput;
      OSP.ParamByName('oEXT').AsObject := Rec;
      OSP.Execute;
    finally
      OSP.Free;
    end;

  OSQL := TOraSQL.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.AutoCommit := True;
    OSQL.ParamCheck := True;

    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add('SELECT VALUE(T)');
    OSQL.SQL.Add('INTO :P_ITEM');
    OSQL.SQL.Add('FROM T_ITEM T');
    OSQL.SQL.Add('WHERE T.ID = 1;');
    OSQL.SQL.Add('END;');

    OSQL.ParamByName('P_ITEM').DataType := ftObject;
    OSQL.ParamByName('P_ITEM').ParamType := ptOutput;
    OSQL.ParamByName('P_ITEM').AsObject := rItem;
    rItem.AllocObject;
    OSQL.Execute;
  finally
    OSQL.Free;
  end;


  Edit1.Text := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsString['VCH'];
  ID := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsInteger['ID'];
  
  // The below two lines access the same DATE field value but the second line causes an error!
  dateItem := rItem.AttrAsDateTime['CREDATE'];
  dateRecItem := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsDateTime['CREDATE'];
end;

procedure TForm1.btnWriteDataClick(Sender: TObject);
var
  OSQL: TOraSQL;
  K: Integer;
begin
  K := Rec.AttrAsArray['ITEMLIST'].AppendItem;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsInteger['ID'] := ID+100;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsInteger['HEADID'] := HeadID;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsString['VCH'] := Edit1.Text;
  Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsInteger['EXTSTATUS'] := 2;
  OSQL := TOraSql.Create(nil);
  try
    OSQL.Session := OraSession;
    OSQL.AutoCommit := True;
    OSQL.ParamCheck := True;

    OSQL.SQL.Add('BEGIN');
    OSQL.SQL.Add('P_HEAD_EXT(:iEXT);');
    OSQL.SQL.Add('END;');

    OSQL.ParamByName('iEXT').DataType := ftObject;
    OSQL.ParamByName('iEXT').ParamType := ptInputOutput;
    OSQL.ParamByName('iEXT').AsObject := Rec;

    // Here TP_HEAD_EXT.ITEMLIST(3).REC object has value (NOT NULL)
    ShowMessage('Item ' + IntToStr(K + 1) + ': ' + Rec.AttrAsArray['ITEMLIST'].ItemAsObject[K].AttrAsObject['REC'].AttrAsString['VCH']);

    OSQL.Execute; // In P_HEAD_EXT procedure ITEMLIST(3).REC IS NULL!
  finally
    OSQL.Free;
  end;
end;

end.

Call btnReadDataClick to see the problem! This code has worked before with ODAC 9.x version.

Best regards,

Balázs Miereisz
WINFORM Kft.

Posted: **Fri 02 Jun 2017 13:54**

Thank you for the information. We will investigate its origin. We will inform you about the results shortly.

Posted: **Fri 15 Sep 2017 11:26**

We have already fixed the error, and the fix will be included in the next ODAC build. We plan to release the build next week.

Posted: **Fri 15 Sep 2017 11:26**

We have already fixed the error, and the fix will be included in the next ODAC build. We plan to release the build next week.

Posted: **Wed 15 Nov 2017 08:02**

Hi Team!

We have just purchased ODAC Professional team license upgrade to the latest version (10.1.3).

You wrote on 15th September:

MaximG wrote:We have already fixed the error, and the fix will be included in the next ODAC build. We plan to release the build next week.

The problem is still exists in this version and we have to use the 9.x version of ODAC.

You can find the code examples in my latest post to this topic.

In the Delphi code example the following line causes the error:

dateRecItem := Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsDateTime['CREDATE'];

It is because Rec.AttrAsArray['ITEMLIST'].ItemAsObject[0].AttrAsObject['REC'].AttrAsIsNull['CREDATE'] = False, but it is NULL in the database.

Both .AttrIsNull['INTEG'] = True and .AttrIsNull['HEADID'] = True, but these values are NOT NULL in the database.

Best Regards,

Balázs Miereisz
WINFORM Kft.

Devart Forums

TOraArray problem with Delphi 32-bit VCL

TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL

Re: TOraArray problem with Delphi 32-bit VCL