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.
but declared as NOT FINAL object we get the same access violation error as before