How to return Oracle Cursor from stored proc as Client Dataset using Delphi and DBExpress

Question

1st off I am Still a little green to Delphi so this might be a "mundane detail" that's being over looked. [sorry in advance]

I have a need to create a TSQLDataset or TClientDataSet from an Oracle 11g cursor contained in a package. I am using Delphi XE2 and DBExpress to connect to the DB and DataSnap to send the data back to the client.

I'm having problems executing the stored procedure from the Delphi code.

Package Head:

create or replace
PACKAGE KP_DATASNAPTEST AS 
  procedure GetFaxData(abbr varchar2, Res out SYS_REFCURSOR);
END KP_DATASNAPTEST; 

Package Body:

create or replace
PACKAGE  body KP_DATASNAPTEST AS 
  procedure GetFaxData(abbr varchar2, Res out SYS_REFCURSOR)is
  Begin
    open Res for 
      SELECT  Name, 
              Address1, 
              City, 
              fax_nbr 
      FROM    name 
      JOIN phone on name.Abrv = phone.abrv 
      WHERE phone.fax_nbr is not null and name.abrv = abbr;
  end;
END KP_DATASNAPTEST;

I have no problem executing this procedure in SQL Developer the problem resides in this code on the DataSnap server:

function TKPSnapMethods.getCDS_Data2(): OleVariant;
var
  cds: TClientDataSet;      
  dsp: TDataSetProvider;
  strProc: TSQLStoredProc;      
begin
  strProc := TSQLStoredProc.Create(self);
  try
    strProc.MaxBlobSize := -1;
    strProc.SQLConnection:= SQLCon;//TSQLConnection

    dsp := TDataSetProvider.Create(self);
    try
      dsp.ResolveToDataSet := True;
      dsp.Exported := False;
      dsp.DataSet := strProc;
      cds := TClientDataSet.Create(self);
      try
        cds.DisableStringTrim := True;
        cds.ReadOnly := True;
        cds.SetProvider(dsp);

        strProc.Close;
        strProc.StoredProcName:= 'KP_DATASNAPTEST.GetFaxData';
        strProc.ParamCheck:= true;
        strProc.ParamByName('abbr').AsString:= 'ZZZTOP';
        strProc.Open; //<--Error: Parameter 'Abbr' not found. 

        cds.Open;
        Result := cds.Data;
      finally
        FreeAndNil(cds);
      end;
    finally
      FreeAndNil(dsp);
    end;
  finally
    FreeAndNil(strProc);
    self.SQLCon.Close;
  end;
end;

I have also tried assigning the param value through the ClientDataSet without any luck. I would not be apposed to returning a TDataSet from the function if its easier or produces results. The data is used to populate custom object attributes.

Answer 1

As paulsm4 mentioned in this answer, Delphi doesn't care about getting stored procedure parameter descriptors, and so that you have to it by yourself. To get params of the Oracle stored procedure from a package, you can try to use the GetProcedureParams method to fill the list with parameter descriptors and with the LoadParamListItems procedure fill with that list Params collection. In code it might look like follows.

Please note, that following code was written just in browser according to documentation, so it's untested. And yes, about freeing ProcParams variable, this is done by the FreeProcParams procedure:

var
  ProcParams: TList;
  StoredProc: TSQLStoredProc;
  ...
begin
  ...
  StoredProc.PackageName := 'KP_DATASNAPTEST';
  StoredProc.StoredProcName := 'GetFaxData';
  ProcParams := TList.Create;
  try
    GetProcedureParams('GetFaxData', 'KP_DATASNAPTEST', ProcParams);
    LoadParamListItems(StoredProc.Params, ProcParams);
    StoredProc.ParamByName('abbr').AsString := 'ZZZTOP';
    StoredProc.Open;
  finally
    FreeProcParams(ProcParams);
  end;
  ...
end;

Answer 2

I don't think Delphi will automagically recognize Oracle parameter names and fill them in for you. I think you need to add the parameters. For example:

with strProc.Params.Add do
 begin
   Name := 'abbr';
   ParamType := ptInput;
   Value := ZZZTOP';
   ...
 end;