如何从存储过程中返回Oracle Cursor作为客户端数据集使用Delphi和DBExpress [英] How to return Oracle Cursor from stored proc as Client Dataset using Delphi and DBExpress

问题描述

第一关我还是一个绿色的德尔福所以这可能是一个平凡的细节，正在过去看。 [提前处理]

我需要从包含在一个包中的Oracle 11g游标创建一个TSQLDataset或TClientDataSet。我使用Delphi XE2和DBExpress连接到DB和DataSnap将数据发送回客户端。

我有问题从Delphi执行存储过程

  create或replace 
 PACKAGE KP_DATASNAPTEST AS 
过程GetFaxData（abbr varchar2，Res out SYS_REFCURSOR）; 
 END KP_DATASNAPTEST; 
  

套件内容：

 创建或替换
 PACKAGE主体KP_DATASNAPTEST AS 
过程GetFaxData（abbr varchar2，Res out SYS_REFCURSOR）是
开始
打开Res 
 SELECT Name，
 Address1，
 City，
 fax_nbr 
 FROM name 
 JOIN phone on name.Abrv = phone.abrv 
 WHERE phone.fax_nbr不为null，name.abrv = abbr; 
 end; 
 END KP_DATASNAPTEST; 
 

在SQL Developer中执行此过程没有问题，该问题存在于DataSnap服务器上的此代码中：

  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; //<  - 错误：未找到参数'Abbr'。 
 
 cds.Open; 
结果：= cds.Data; 
 finally 
 FreeAndNil（cds）; 
 end; 
 finally 
 FreeAndNil（dsp）; 
 end; 
 finally 
 FreeAndNil（strProc）; 
 self.SQLCon.Close; 
 end; 
 end; 
  



我也试过通过ClientDataSet分配参数值，没有任何运气。
如果更容易或产生结果，我不会诉诸从函数返回TDataSet。该数据用于填充自定义对象属性。

解决方案

像 GetProcedureParams 方法使用参数描述符和 LoadParamListItems 过程填充该列表 Params 集合。在代码中它可能如下所示。

请注意，下面的代码是根据文档在浏览器中编写的，因此未经测试。是的，关于释放 ProcParams 变量，这是通过 FreeProcParams 程序：

  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; 
  

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.

解决方案

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;

这篇关于如何从存储过程中返回Oracle Cursor作为客户端数据集使用Delphi和DBExpress的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！