TOraStoredProc with AttrAsArray BUG !

[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.

[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

[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.

[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.

[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 ???

[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.