Page 1 of 1
Posted: Tue 05 Jul 2005 07:23
by Alex
Please specify your Oracle Client/Server version and table creation script with all included types. Also show your storedproc call routine with all preparatory object creation.
Posted: Tue 05 Jul 2005 16:26
by Guest
I use Oracle 9i(serveur and client).
--Oracle source
CREATE OR REPLACE PACKAGE pck_cpy
IS
PROCEDURE upd(data IN typ_cpy_data);
END;
/
CREATE OR REPLACE PACKAGE BODY pck_cpy
IS
PROCEDURE upd(data IN typ_cpy_data)
IS
BEGIN
NULL; -- Do stuff with data, update tables etc...
END;
END;
/
CREATE OR REPLACE TYPE typ_cpy_data AS OBJECT (
cpy_id NUMBER
,cpy_list typ_cpy_list)
/
CREATE OR REPLACE TYPE typ_cpy_list AS TABLE OF typ_cpy_rec
/
CREATE OR REPLACE TYPE typ_cpy_rec AS OBJECT (
rec_id NUMBER)
/
-- Delphi source
// Part that is called once in the FormCreate event
Data: TOraObject;
with oraStoredProc do begin
CreateProcCall('pck_cpy.upd');
ParamByName('data').AsObject.AllocObject(oraSession.OCISvcCtx, 'typ_cpy_data');
Data := ParamByName('data').AsObject;
end;
// Part that is called more than once
With Data do begin
AttrAsInteger['cpy_id'] := 10;
AttrAsObject['cpy_list'].AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
for i:= 0 to 5 do begin
AttrAsArray['cpy_list'].ItemAsObject.AppendItem;
AttrAsArray['cpy_list'].ItemAsObject.AllocObject(oraSession.OCISvcCtx, 'typ_fig_rec');
AttrAsArray['cpy_list'].ItemAsObject.AttrAsInteger['rec_id'] := i;
end;
end;
oraSession.StartTransaction;
oraStoredProc.Execute;
oraSession.Commit;
On the first execution, it is perfect...
If i want to execute a second time this second part but with no value in the "cpy_list" parameter,
the "cpy_list" parameter still holds the 6 items...
I would like to do some thing like AttrAsArray['cpy_list'].ItemAsObject.Clear !!!
Thank you for your help !
Olivier
Posted: Thu 07 Jul 2005 14:34
by Alex
We got your request and now examines the problem. Unfortunately now we couldn't give you any information. As soon as we solve the problem, we'll let you know.
Posted: Fri 08 Jul 2005 11:15
by Alex
The following code may help yo to solve your problem, instead of
AttrAsArray['cpy_list'].ItemAsObject.Clear you can execute such code
Code: Select all
AttrAsObject['cpy_list'].FreeObject(True);
AttrAsObject['cpy_list'].AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
this recreates
'cpy_list' object and thus clears all its fields.
If you have any future questions please inform us.
Posted: Wed 13 Jul 2005 13:54
by olivier.huber
Thank you so much for your quick answer.
But infortunately, i have already tried this solution and it seems that the AllocObject manage to retrieve its old values...even if it has been previously freed !
By the way, i am unable to login to the forum in spite of my good username and password ???
Posted: Fri 15 Jul 2005 11:44
by Alex
You are right such Clear method doesn't work with arrays that are object fields, to resolve your situation I propose using following method:
Code: Select all
-- Delphi source
// Part that is called once in the FormCreate event
Data: TOraObject;
Arr : TOraArray;
with oraStoredProc do begin
CreateProcCall('pck_cpy.upd');
ParamByName('data').AsObject.AllocObject(oraSession.OCISvcCtx, 'typ_cpy_data');
Data := ParamByName('data').AsObject;
end;
// Part that is called more than once
With Data do begin
AttrAsInteger['cpy_id'] := 10;
//>>Instead of AttrAsObject['cpy_list'].AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
Arr := TOraArray.Create;
Arr.AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
AttrAsObject['cpy_list'].Assign(Arr);
//>>
for i:= 0 to 5 do begin
AttrAsArray['cpy_list'].ItemAsObject[i].AppendItem;
AttrAsArray['cpy_list'].ItemAsObject[i].AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
AttrAsArray['cpy_list'].ItemAsObject[i].AttrAsInteger['rec_id'] := i;
end;
end;
oraSession.StartTransaction;
oraStoredProc.Execute;
oraSession.Commit;
// Part that is called when you need to pass clear array
With Data do begin
AttrAsInteger['cpy_id'] := 10;
//>> Here we clear array that is object field
Arr.FreeObject;
Arr.AllocObject(oraSession.OCISvcCtx, 'typ_cpy_list');
AttrAsObject['cpy_list'].Assign(Arr);
//>>
end;
oraSession.StartTransaction;
oraStoredProc.Execute;
oraSession.Commit;
If you encounter any problems with this solution please inform us.
P.S. We are planinig to include TOraArray.Clear functionality in one of the next ODAC builds.