Re: Oracle with Crystal Reports 11.5.3

Jer wrote:
> Hi,
> I am trying to develop a report to call an oracle stored procedure
> contained within a package and am having major trouble in that crystal
> cannot seem to determine the output ref cursor at all... I think I
> have tried just about everything!!!
>
> Here is my code:
>
> Package spec:
> CREATE OR REPLACE PACKAGE p_TradeReport_NEW
> AS
>
> TYPE group_trade_list IS REF CURSOR RETURN V_GROUP_TRADES%ROWTYPE;
>
> PROCEDURE sp_getGroupTrades
> (
> rpt_cursor IN OUT group_trade_list,
> in_StartDate IN DATE,
> in_EndDate IN DATE,
> in_ParentAccountCode IN VARCHAR
> );
>
> END p_TradeReport_NEW;
>
> Package Body:
> CREATE OR REPLACE PACKAGE BODY p_Tradereport_NEW
> AS
> PROCEDURE sp_getGroupTrades
> (
> rpt_cursor IN OUT group_trade_list,
> in_StartDate IN DATE,
> in_EndDate IN DATE,
> in_ParentAccountCode IN VARCHAR
> )
> AS
> BEGIN
> OPEN rpt_cursor FOR
> SELECT trade_date,
> parent_acct_cd,
> trans_type,
> exec_qty,
> cusip,
> sec_name,
> settle_date,
> coupon_rate,
> s_p,
> moody,
> MATURE_DATE,
> exec_price,
> yield_to_mature,
> exec_amt,
> duration,
> spread_duration,
> limit_price,
> sec_typ_cd
> FROM V_GROUP_TRADES
> WHERE parent_acct_cd = in_ParentAccountCode
> AND trade_date BETWEEN in_StartDate AND in_EndDate;
> END sp_getGroupTrades;
>
> END p_Tradereport_NEW;
>
>
>
> The problem is when I connect to the database via Crystal I use the
> MSDAORA (Oracle OLE DB) provider, after connecting I am able to select
> my procedure and get its resultset however I am not able to do
> anything with the results at all. Also when I have Crystal "Verify
> the database" it tells me that the database table
> "P_TRADEREPORT_NEW.SP_GETGROUPTRADES" cannot be found and proceeds to
> remove it from my report...
>
> Anyone have any idea what I am doing wrong, or what additionally I
> need to do here to get it working???
>
> Thanks
>
>
> Jeremy.

Go to Morgan's Library at www.psoug.org
Click on REF CURSORS ... the demo for Strongly Type REF CURSORS works with Crystal.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org