optimized passing Collection with TOraSQL

[opylypcz]

Dear ODAC Team,

I have Delphi project which intensively uses PL/SQL procedures that works with Oracle Nested Tables based on simple types. Below i attached sample procedure code.

For each Collection type parameter i have to use "AsTable.AllocObject()". Calling that method is slow. Can i optimize it somehow to load collection type to application memory once at application start and then use that allocated-cached object without need to call "AsTable.AllocObject()".

regards
Alex

Code: Select all

// CREATE OR REPLACE TYPE T_VARCHAR2_255_TAB AS TABLE OF VARCHAR2(255);

procedure saveList(aList : TArray<String>; aSession : TOraSession);
var
  l : TOraSQL;
  i, lIndex : integer;
begin
  l := TOraSQL.Create(nil);
  l.Text :=
    'begin ' +
    'save_list(:aList);' +
    'end;';
  l.Session := aSession;

  l.ParamByName('aList').DataType := ftDataSet;
  l.ParamByName('aList').ParamType := ptInput;
  l.ParamByName('aList').AsTable.OCISvcCtx:= session.OCISvcCtx;
  [b]l.ParamByName('aList').AsTable.AllocObject('T_VARCHAR2_255_TAB');[/b]
  for i := 0 to length(aList) - 1 do
  begin
    lIndex := l.ParamByName('aList').AsTable.AppendItem();
    l.ParamByName('aList').AsTable.itemAsString[lIndex] := aList[i];
  end;

  l.Execute();

  l.Params.Clear;
  l.free;
end;

[AlexP]

Hello,

For this, you can use TOraNestTable and the AllocObject method

Code: Select all

var
OraNestTable: TOraNestTable;
begin
  OraNestTable := TOraNestTable.Create(TOraType.Create(OraSession.OCISvcCtx, 'T_VARCHAR2_255_TAB'));
  OraNestTable.AllocObject;

....

and pass the created object as a parameter to your procedure

Code: Select all

// CREATE OR REPLACE TYPE T_VARCHAR2_255_TAB AS TABLE OF VARCHAR2(255);

procedure saveList(aList : TArray<String>; aSession : TOraSession; OraNestTable: TOraNestTable);
var
  l : TOraSQL;
  i, lIndex : integer;
begin
  l := TOraSQL.Create(nil);
  l.Text :=
    'begin ' +
    'save_list(:aList);' +
    'end;';
  l.Session := aSession;

  l.ParamByName('aList').AsTable := OraNestTable;
  for i := 0 to length(aList) - 1 do
  begin
    lIndex := l.ParamByName('aList').AsTable.AppendItem();
    l.ParamByName('aList').AsTable.itemAsString[lIndex] := aList[i];
  end;

  l.Execute();

  l.Params.Clear;
  l.free;
end;

[opylypcz]

can i use the same technique if my procedure have many collection based parameters but only one allocated object?

Code: Select all

// CREATE OR REPLACE TYPE T_VARCHAR2_255_TAB AS TABLE OF VARCHAR2(255);

procedure saveList(aList1 : TArray<String>; 
                   aList2 : TArray<String>; 
                   ...
                   aListN : TArray<String>; 
                   aSession : TOraSession);
var
  l : TOraSQL;
  i, lIndex : integer;
begin
  l := TOraSQL.Create(nil);
  l.Text :=
    'begin ' +
    'save_list(:aList);' +
    'end;';
  l.Session := aSession;

  l.ParamByName('aList1').AsTable := OraNestTable;
  for i := 0 to length(aList1) - 1 do
  begin
    lIndex := l.ParamByName('aList1').AsTable.AppendItem();
    l.ParamByName('aList1').AsTable.itemAsString[lIndex] := aList1[i];
  end;

  l.ParamByName('aList2').AsTable := OraNestTable;
  for i := 0 to length(aList2) - 1 do
  begin
    lIndex := l.ParamByName('aList2').AsTable.AppendItem();
    l.ParamByName('aList2').AsTable.itemAsString[lIndex] := aList2[i];
  end;

  ...

  l.ParamByName('aListN').AsTable := OraNestTable;
  for i := 0 to length(aListN) - 1 do
  begin
    lIndex := l.ParamByName('aListN').AsTable.AppendItem();
    l.ParamByName('aListN').AsTable.itemAsString[lIndex] := aListN[i];
  end;

  l.Execute();

  l.Params.Clear;
  l.free;
end;

[AlexP]

Yes, you can use the same TOraNestTable, but you should call the Clear method of OraNestTable after each loop to clear the previous records. (this method doesn't call AllocObject).

[opylypcz]

Thank you for help !

[AlexP]

You are welcome. Feel free to contact us if you have any further questions.

[opylypcz]

Unfortunately, it doesnt work as I intended.

The thing i want to acheive is:
- alloc object type once (only once during all application instance lifetime)
- and use this type for procedure with many parameters, like:

Code: Select all

procedure test_list(l1 in T_VARCHAR2_255_TAB, l2 in T_VARCHAR2_255_TAB) as ...

is it possible?

[AlexP]

No. If you have several parameters of such type, you should create the same number of objects.

[opylypcz]

i think i solved my problem, anyway thanks for help

Code: Select all

var
  lOraType : TOraType;
  lOraNestTable1, lOraNestTable2 : TOraNestTable;
begin
  // this line is the key
  // if I make lOraType as global variable and call this line only once 
  // then problem is solved
  lOraType := TOraType.Create(OraSession1.OCISvcCtx, 'T_VARCHAR2_255_TAB'); 

  OraNestTable1 := TOraNestTable.Create(lOraType);
  OraNestTable1.AllocObject;

  OraNestTable2 := TOraNestTable.Create(lOraType);
  OraNestTable2.AllocObject;
  ...

[AlexP]

In your last sample you are calling AllocObject several times, just what you wanted to avoid.

[opylypcz]

The thing I wanted to avoid/reduce is "implicit Collection type loading from Oracle Database dictionary by ODAC".

I was convicted, that mentioned above implicit action is done when i call "AllocObject" method. I have no Source Edition, so i dont know what exactly is going on inside - for me its a black box. Also its difficult to find out this from official documentation / demos.

One more time thanks for help.

[AlexP]

When calling the AllocObject method, we are calling the OCIObjectNew Oracle API function.