Page 1 of 1
Oracle SP table type param, UniDac vs ODac
Posted: Sat 04 Apr 2020 11:15
by tcxbalage
Let's say I have an ORACLE stored procedure with a table type param.
Code: Select all
create or replace type t_number_tbl is table of number;
create or replace procedure dyn_param_test_1
(
io_params in out t_number_tbl
)
as
begin
...
end;
/
When I was using ODAC 10.0.2 with Delphi XE7 it was easy to manage this type of params:
Code: Select all
...
stoProc: TOraStoredProc;
...
stoProc.ParamByName('io_params').Length := 2;
stoProc.ParamByName('io_params').ItemAsFloat[1] := 100;
stoProc.ParamByName('io_params').ItemAsFloat[2] := 42;
...
Right now I'm using UniDAC 7.4.12 with Delphi 7 (ORACLE also), but unfortunatelly I haven't found any way to achieve the above described functionallity.
So, my question how can I handle Oracle table type params using UniDAC 7.4.12 + Delphi 7?
Re: Oracle SP table type param, UniDac vs ODac
Posted: Tue 07 Apr 2020 08:18
by MaximG
The needed feature is available in UniDAC 8.1. This code sample demonstrates it:
Code: Select all
...
stoProc: TUniStoredProc;
...
stoProc.Params.ValueCount := 2;
stoProc.Params[0].Values[0].AsFloat := 100;
stoProc.Params[0].Values[1].AsFloat := 42;
...
Re: Oracle SP table type param, UniDac vs ODac
Posted: Tue 07 Apr 2020 10:22
by tcxbalage
Thank you for the quick response.
I already found these properties in UniDAC 7.4.12, but not works, when I try to set the ValueCount prop I got an error message:
"Data type is not allowed for a DML array."
So - if I understand you correctly - this functionallity has beeen fixed/improved in the 8.1 version?
Re: Oracle SP table type param, UniDac vs ODac
Posted: Tue 07 Apr 2020 14:00
by MaximG
Yes, this functionality is available in the latest versions of UniDAC
Re: Oracle SP table type param, UniDac vs ODac
Posted: Tue 07 Apr 2020 14:06
by tcxbalage
Thanks again!
Re: Oracle SP table type param, UniDac vs ODac
Posted: Wed 21 Apr 2021 10:04
by tcxbalage
Finally we were able to upgrade to the latest version (8.4.3) so I just tried the suggested code, but I''m affraid it's not working.
ORACLE:
Code: Select all
create or replace type t_number_tbl is table of number;
create or replace procedure dyn_param_test_1
(
io_params in out t_number_tbl
)
as
begin
null;
end;
Delphi dfm:
Code: Select all
object conMain: TUniConnection
AutoCommit = False
ProviderName = 'Oracle'
Port = 1521
SpecificOptions.Strings = (
'Oracle.Direct=True')
Username = ...
Server = ...
Connected = True
LoginPrompt = False
end
object trMain: TUniTransaction
DefaultConnection = conMain
end
object spTest: TUniStoredProc
StoredProcName = 'DYN_PARAM_TEST_1'
SQL.Strings = (
'begin'
' DYN_PARAM_TEST_1(:IO_PARAMS);'
'end;')
Connection = conMain
Transaction = trMain
ParamData = <
item
DataType = ftUnknown
Name = 'IO_PARAMS'
ParamType = ptInputOutput
end>
CommandStoredProcName = 'DYN_PARAM_TEST_1'
end
Delphi pas, test case 1:
Code: Select all
procedure TfrmMain.Button1Click(Sender: TObject);
begin
conMain.Connect;
spTest.Params.ValueCount := 7;
spTest.ParamByName('io_params').Values[0].AsFloat := 20;
spTest.ParamByName('io_params').Values[1].AsFloat := 120;
spTest.ParamByName('io_params').Values[2].AsFloat := 220;
spTest.ParamByName('io_params').Values[3].AsFloat := 320;
spTest.ParamByName('io_params').Values[4].AsFloat := 420;
spTest.ParamByName('io_params').Values[5].AsFloat := 520;
spTest.ParamByName('io_params').Values[6].AsFloat := 620;
spTest.Execute;
end;
Delphi pas, test case 2:
Code: Select all
procedure TfrmMain.Button1Click(Sender: TObject);
begin
conMain.Connect;
spTest.ParamByName('io_params').ValueCount := 7;
spTest.ParamByName('io_params').Values[0].AsFloat := 20;
spTest.ParamByName('io_params').Values[1].AsFloat := 120;
spTest.ParamByName('io_params').Values[2].AsFloat := 220;
spTest.ParamByName('io_params').Values[3].AsFloat := 320;
spTest.ParamByName('io_params').Values[4].AsFloat := 420;
spTest.ParamByName('io_params').Values[5].AsFloat := 520;
spTest.ParamByName('io_params').Values[6].AsFloat := 620;
spTest.Execute;
end;
result in both case: exception
message:
ORA-06550: line 2, column3:
PLS-00306: wrong number or types of arguments in call to 'DYN_PARAM_TEST_1'
ORA-06550: line 2, column3:
PL/SQL: statement ignored
Can you provide me a working sample code please?
Re: Oracle SP table type param, UniDac vs ODac
Posted: Fri 18 Jun 2021 12:55
by MaximG
Sorry for the late reply. Yes, we have reproduced the issue. We'll get back to you with an update soon.
Re: Oracle SP table type param, UniDac vs ODac
Posted: Thu 22 Jul 2021 09:21
by tcxbalage
May I know when to expect the fix for this issue?
We are waiting for it ages ago.
Re: Oracle SP table type param, UniDac vs ODac
Posted: Fri 30 Jul 2021 17:45
by MaximG
The following code sample demonstrates the behavior that interests you :
Code: Select all
uses OraObjectsUni;
...
var
OraArray: TOraArray;
begin
...
UniStoredProc.StoredProcName := 'dyn_param_test';
UniStoredProc.Prepare;
UniStoredProc.ParamByName('io_params').DataType := ftArray;
OraArray := UniStoredProc.ParamByName('io_params').AsArray as TOraArray;
OraArray.AllocObject('t_number_tbl');
OraArray.ItemAsFloat[1] := 20;
OraArray.ItemAsFloat[1] := 120;
OraArray.ItemAsFloat[2] := 220;
OraArray.ItemAsFloat[3] := 320;
OraArray.ItemAsFloat[4] := 420;
OraArray.ItemAsFloat[5] := 520;
OraArray.ItemAsFloat[6] := 620;
UniStoredProc.Execute;
end;
Re: Oracle SP table type param, UniDac vs ODac
Posted: Tue 03 Aug 2021 07:35
by tcxbalage
works fine, thank you!