Calling package funnction which returns array

[Marko Horvat]

Hello,

I cant make returning array from PACKAGE function work. Can anyone help with that.

My package example:
PACKAGE:
CREATE OR REPLACE
PACKAGE TEST_PKG AS
TYPE str_arr IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
FUNCTION getArray return str_arr;

END TEST_PKG;

Package body:
CREATE OR REPLACE
PACKAGE BODY TEST_PKG AS

FUNCTION getArray return str_arr AS
tmp str_arr;
BEGIN
TEST_PKG.getArray
tmp(0):='test0';
tmp(1):='test1';
tmp(2):='test2';
return tmp;
END getArray;

END TEST_PKG;

So how would Delphi code be to retrieve this array? I use Delphi Berlin

thank you for help, Marko

 

[Marco Kalter]

Here is an example:

procedure TMainForm.ButtonClick(Sender: TObject);
var Q: TOracleQuery;
    Arr: Variant;
    i: Integer;
begin
  Q := TOracleQuery.Create(nil);
  Q.Session := OracleSession1;
  Q.SQL.Add('begin');
  Q.SQL.Add('  :arr := test_pkg.getarray;');
  Q.SQL.Add('end;');
  Q.DeclareVariable('arr', otString);
  Q.DimPLSQLTable('arr', 3, 128);
  Q.Execute;
  Arr := Q.GetVariable('arr');
  for i := VarArrayLowBound(Arr, 1) to VarArrayHighBound(Arr, 1) do
    ShowMessage(Arr[i]);
  Q.Free;
end;

Note however that the PL/SQL Table must be 1-based. Item 0 cannot be retrieved from the application and will cause an exception. Your code should be changed to:

tmp(1):='test1';
tmp(2):='test2';
tmp(3):='test3';

 

[Marko Horvat]

Thanks for help.

Since then i found some easy solutions. If I have package i can build easy code wrapper in Delphi menu: oracle->Package wizard.

Marko