Oracle SP table type param, UniDac vs ODac

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Sat 04 Apr 2020 11:15

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?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Oracle SP table type param, UniDac vs ODac

Post by MaximG » Tue 07 Apr 2020 08:18

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

tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Re: Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Tue 07 Apr 2020 10:22

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?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Oracle SP table type param, UniDac vs ODac

Post by MaximG » Tue 07 Apr 2020 14:00

Yes, this functionality is available in the latest versions of UniDAC

tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Re: Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Tue 07 Apr 2020 14:06

Thanks again!

tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Re: Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Wed 21 Apr 2021 10:04

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?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Oracle SP table type param, UniDac vs ODac

Post by MaximG » Fri 18 Jun 2021 12:55

Sorry for the late reply. Yes, we have reproduced the issue. We'll get back to you with an update soon.

tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Re: Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Thu 22 Jul 2021 09:21

May I know when to expect the fix for this issue?
We are waiting for it ages ago.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Oracle SP table type param, UniDac vs ODac

Post by MaximG » Fri 30 Jul 2021 17:45

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;

tcxbalage
Posts: 23
Joined: Sat 04 Apr 2020 10:45

Re: Oracle SP table type param, UniDac vs ODac

Post by tcxbalage » Tue 03 Aug 2021 07:35

works fine, thank you!

Post Reply