CREATE OR REPLACE PACKAGE REPORT IS
 TYPE E_REF_CUR IS REF CURSOR;
 FUNCTION CALCULATE_JOB_COST(E_ID IN VARCHAR2) RETURN NUMBER; 
 PROCEDURE REPORT_JOB_COST(E_CUR OUT E_REF_CUR);
END REPORT;

CREATE OR REPLACE PACKAGE BODY REPORT IS
 FUNCTION CALCULATE_JOB_COST(E_ID IN VARCHAR2) RETURN NUMBER IS
  SUM_COST NUMBER;
 BEGIN
  SELECT SUM(RESOURCE_COST_PERMONTH)
    INTO SUM_COST
    FROM JOB_USE_RESOURCE, USE_RESOURCE
   WHERE (JUR_JOB_ID = UPPER(E_ID) AND JUR_RESOURCE_ID = RESOURCE_ID);
 
  -- if sum_cost is null, set sum_cost = -1 
  IF SUM_COST IS NULL THEN
   SUM_COST := -1;
  END IF;
 
  RETURN SUM_COST;
 END CALCULATE_JOB_COST;     PROCEDURE REPORT_JOB_COST(E_CUR OUT E_REF_CUR) IS
 BEGIN
  IF E_CUR%ISOPEN THEN
   CLOSE E_CUR;
  END IF;
  
  OPEN E_CUR FOR
   SELECT EMPLOYEE_ID,
          EMPLOYEE_NAME,
          JOB_NAME,
          CALCULATE_JOB_COST(EMPLOYEE_JOBID) AS JOB_COST
     FROM EMPLOYEE, JOB
    WHERE EMPLOYEE_JOBID = JOB_ID;
 END REPORT_JOB_COST;
END REPORT;

  ......
  with StoredProc1 do
    begin
      Close;
      StoredProcName :='REPORT.REPORT_JOB_COST';
      Params.Clear;
      Params.CreateParam(ftCursor,'E_CUR',ptOutput);
      Prepare;
      try
        Open;
      except
        on E:EDatabaseError do ShowMessage(E.Message);
      end;
      Close;
    end;
  ......

引言: 依據online help: TStoredProc reuses the Params property to hold the results returned by a stored procedure 所以,你應該是把stored procedure的參數設成output的方式來取得回傳值才是

 PROCEDURE REPORT_JOB_COST(E_ID       OUT VARCHAR2,
                           E_NAME     OUT VARCHAR2,
                           E_JOB_NAME OUT VARCHAR2,
                           E_JOB_COST OUT NUMBER) IS
 BEGIN
  SELECT EMPLOYEE_ID,
         EMPLOYEE_NAME,
         JOB_NAME,
         CALCULATE_JOB_COST(EMPLOYEE_JOBID) AS JOB_COST
    INTO E_ID, E_NAME, E_JOB_NAME, E_JOB_COST
    FROM EMPLOYEE, JOB
   WHERE EMPLOYEE_JOBID = JOB_ID;
 END REPORT_JOB_COST;

CREATE OR REPLACE PROCEDURE PG_TEST
(PARAM_A IN        VARCHAR := NULL,
 RTN_MSG OUT        VARCHAR2)IS         /*輸出的參數:結果訊息*/
BEGIN
  RTN_MSG := '傳入的參數是:'||PARAM_A
END;

引言: 看起來像是store procedure的問題,你要不要把store procedure改成簡單一點測試...

CREATE OR REPLACE PROCEDURE PG_TEST
(PARAM_A IN        VARCHAR := NULL,
 RTN_MSG OUT        VARCHAR2)IS         /*輸出的參數:結果訊息*/
BEGIN
  RTN_MSG := '傳入的參數是:'||PARAM_A
END;

引言:

引言: 依據online help: TStoredProc reuses the Params property to hold the results returned by a stored procedure 所以,你應該是把stored procedure的參數設成output的方式來取得回傳值才是

對不起，我不太了解。您的意思是要把 Stored Procedure 改成

 PROCEDURE REPORT_JOB_COST(E_ID       OUT VARCHAR2,
                           E_NAME     OUT VARCHAR2,
                           E_JOB_NAME OUT VARCHAR2,
                           E_JOB_COST OUT NUMBER) IS
 BEGIN
  SELECT EMPLOYEE_ID,
         EMPLOYEE_NAME,
         JOB_NAME,
         CALCULATE_JOB_COST(EMPLOYEE_JOBID) AS JOB_COST
    INTO E_ID, E_NAME, E_JOB_NAME, E_JOB_COST
    FROM EMPLOYEE, JOB
   WHERE EMPLOYEE_JOBID = JOB_ID;
 END REPORT_JOB_COST;

這樣嗎？不過我這樣做之後會出現 exception [Oracle][ODBC][Ora]ORA-01422:精確擷取傳回的列數超過所要求的列數 這要如何解決呢??

引言: 找到了,你必需把TParameter.DataType的值設成ftCursor 你可以看你的Delphi online help,找ADO的TADOStoredProc,因為回傳cursor好像只有ADO有支援,而且只有oracle能這樣做

  with ADOStoredProc1 do
    begin
      Close;
      ProcedureName :='REPORT_JOB_COST';
      Parameters.Clear;
      Parameters.CreateParameter('E_CUR',ftCursor,pdOutput,0,null);
      try
        ExecProc;
      except
        on E:EDatabaseError do ShowMessage(E.Message);
      end;
      Close;
    end;

create or replace procedure REPORT_JOB_COST
( E_ID IN VARCHAR2,
  c1 out pg_ref_cursor.rpt_cursor
)
is
begin
   open  c1 for
   SELECT EMPLOYEE_ID,
          EMPLOYEE_NAME,
          JOB_NAME,
          CALCULATE_JOB_COST(EMPLOYEE_JOBID) AS JOB_COST
     FROM EMPLOYEE, JOB
    WHERE EMPLOYEE_JOBID = E_ID; 
end;

QBE.Active         := FALSE;
QBE.StoredProcName := REPORT_JOB_COST';
QBE.ParamBindMode  := pbByName;
QBE.Params.CreateParam(ftString, 'E_ID', ptInput);
QBE.Params.CreateParam(ftCursor, 'C1', ptOutput);
QBE.Active         := TRUE;

create or replace procedure REPORT_JOB_COST
( E_ID IN VARCHAR2,
  c1 out pg_ref_cursor.rpt_cursor
)
is
begin
   open  c1 for
   SELECT EMPLOYEE_ID,
          EMPLOYEE_NAME,
          JOB_NAME,
          CALCULATE_JOB_COST(EMPLOYEE_JOBID) AS JOB_COST
     FROM EMPLOYEE, JOB
    WHERE EMPLOYEE_JOBID = E_ID; 
end;

QBE.Active         := FALSE;
QBE.StoredProcName := REPORT_JOB_COST';
QBE.ParamBindMode  := pbByName;
QBE.Params.CreateParam(ftString, 'E_ID', ptInput);
QBE.Params.CreateParam(ftCursor, 'C1', ptOutput);
QBE.Active         := TRUE;

 
create or replace package pg_ref_cursor
 is
   type rpt_cursor is ref cursor;
end;
/    create or replace procedure pg_get_cursor
(
  c1 out pg_ref_cursor.rpt_cursor
)
is
begin
   open  c1 for
   select *
   from USER_OBJECTS;
end;
/ 

 
unit Unit1;    interface    uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, DBTables, StdCtrls, Buttons;    type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    StoredProc1: TStoredProc;
    DBGrid1: TDBGrid;
    BitBtn1: TBitBtn;
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;    var
  Form1: TForm1;    implementation    {$R *.DFM}    procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  StoredProc1.Active:=False;
  StoredProc1.StoredProcName:='PG_GET_CURSOR';
  StoredProc1.Params.Clear;
  StoredProc1.ParamBindMode:=pbByName;
  StoredProc1.Params.CreateParam(ftCursor,'C1',ptOutput);
  StoredProc1.Active:=TRUE;
end;    end.

object Form1: TForm1
  Left = 228
  Top = 203
  Width = 544
  Height = 375
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = True
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 8
    Top = 40
    Width = 521
    Height = 297
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object BitBtn1: TBitBtn
    Left = 88
    Top = 8
    Width = 75
    Height = 25
    Caption = 'Run'
    TabOrder = 1
    OnClick = BitBtn1Click
  end
  object DataSource1: TDataSource
    DataSet = StoredProc1
    Left = 50
    Top = 4
  end
  object StoredProc1: TStoredProc
    DatabaseName = 'REDBILL'
    StoredProcName = 'PG_GET_CURSOR'
    Left = 10
    Top = 2
    ParamData = <
      item
        DataType = ftCursor
        Name = 'C1'
        ParamType = ptOutput
      end>
  end
end

with ADOStoredProc1 do
  begin
    Close;
    ProcedureName := 'REPORT_JOB_COST';
    Parameters.Clear;
    Parameters.CreateParameter('E_CUR',ftCursor,pdOutput,0,null);
    try
      ExecProc;
    except
      on E:EDatabaseError do ShowMessage(E.Message);
    end;
  end;

引言: 感謝 change.jian 大大，我已經解決了。補充一點，這個方法一定要透過 BDE 才能作。yachanga 大大的方法也可以，不過因為本身的限制(一定要使用Stored Procedure)，所以沒辦法利用 yachanga 大大的方法。我現在在研究 ADO 要怎麼做，碰到一個問題。希望有興趣的人來研究一下。

with ADOStoredProc1 do
  begin
    Close;
    ProcedureName := 'REPORT_JOB_COST';
    Parameters.Clear;
    Parameters.CreateParameter('E_CUR',ftCursor,pdOutput,0,null);
    try
      ExecProc;
    except
      on E:EDatabaseError do ShowMessage(E.Message);
    end;
  end;

紅色那一行的 Length 要設多少阿？怎麼設都不對，請了解的大大回答一下。謝謝。

引言: create的參數如為ftCursor的話,不用設長度,看上面那個例子..,另外,用ADO也是可以的,因為我之前是用ADO做的

引言:

引言: create的參數如為ftCursor的話,不用設長度,看上面那個例子..,另外,用ADO也是可以的,因為我之前是用ADO做的

能不能麻煩change.jian大大寫一個ADO的範例，謝謝您了。

 
with ADOStoredProc1 do //這裡不就是ADO的嗎?
  begin
    Close;
    ProcedureName := 'REPORT_JOB_COST';
    Parameters.Clear;
    Parameters.CreateParameter('E_CUR',ftCursor,pdOutput,0,null);
    try
      ExecProc;
    except
      on E:EDatabaseError do ShowMessage(E.Message);
    end;
  end;

引言: 對不起。我發問錯方向了。我想了解change.jian 大大的 ADOConnectString 怎麼設，因為不管我是設 Microsoft OLE DB Provider for Oracle 或是 Oracle Provider for OLE DB 都會產生我之前說的 exception: EOleException: 提供不一致或不完全資訊導致參數物件不適當的被拒。

Provider=MSDAORA.1;Password=XXXXX;Persist Security Info=True;User ID=REDBILL;Data Source=REDBILL.world 我那個時候用的是Microsoft OLE DB Provider for Oracle 建議你從最簡單的試試看,直接拉個TADOConnection及TADOStoredProc,在Delphi裡直接設定TADOStoredProc的StoreProcedureName及相關的參數,應該可以正常才是...(我就是直接拖拉放的)