Page 1 of 1
optimized passing Collection with TOraSQL
Posted: Fri 06 Feb 2015 12:10
by 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;
Re: optimized passing Collection with TOraSQL
Posted: Mon 09 Feb 2015 11:43
by 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;
Re: optimized passing Collection with TOraSQL
Posted: Mon 09 Feb 2015 14:13
by 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;
Re: optimized passing Collection with TOraSQL
Posted: Tue 10 Feb 2015 09:37
by 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).
Re: optimized passing Collection with TOraSQL
Posted: Tue 10 Feb 2015 12:38
by opylypcz
Thank you for help !
Re: optimized passing Collection with TOraSQL
Posted: Wed 11 Feb 2015 08:56
by AlexP
You are welcome. Feel free to contact us if you have any further questions.
Re: optimized passing Collection with TOraSQL
Posted: Wed 11 Feb 2015 14:56
by 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?
Re: optimized passing Collection with TOraSQL
Posted: Thu 12 Feb 2015 10:57
by AlexP
No. If you have several parameters of such type, you should create the same number of objects.
Re: optimized passing Collection with TOraSQL
Posted: Thu 12 Feb 2015 12:01
by 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;
...
Re: optimized passing Collection with TOraSQL
Posted: Fri 13 Feb 2015 10:15
by AlexP
In your last sample you are calling AllocObject several times, just what you wanted to avoid.
Re: optimized passing Collection with TOraSQL
Posted: Fri 13 Feb 2015 11:05
by 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.
Re: optimized passing Collection with TOraSQL
Posted: Mon 16 Feb 2015 09:28
by AlexP
When calling the AllocObject method, we are calling the OCIObjectNew Oracle API function.