Oracle SP/Crystal communication

[bmpsu]

Crystal XI
Oracle 9i
OLE DB (ADO)

I have an Stored Procedure that is the data source for my report. I would like this SP to update the table with the correct data based off the parameter input from the user. It seems that the only time I can get the correct data in the table is when I use the Database:Verify Database command in Crystal. When verifying the database, the table is populated with the correct data based off the date parameter. If I just run the report using the refresh button, this table does not update. I am not too familiar with Oracle SP's, but assume a transaction is not being completed? Any input on the SP below. I have played around using 'commit;' and 'truncate' over 'delete' and not having any luck.

Any help would be much appreciated. Thanks.
Example:

CREATE OR REPLACE PROCEDURE
sp_divcomm_act_report (v_enddate date) as
 v_startmonth number;
 v_startyear number;
 v_endmonth number;
 v_endyear number;
 v_monthinx number;
 v_yearinx number;
 v_prof number := 1;
 v_lictype number := 1003;
 v_dgcitem number := 1002;
 v_cgccitem number := 622;
 v_firstdate date;
 vv_new_apps number;

BEGIN

 DELETE FROM divcomm_act_report;

  select min(checklist_history.entry_date)
    INTO v_firstdate
    FROM checklist_history,
         license
   WHERE checklist_history.license_id = license.license_id and
         license.profession_id = v_prof AND
         license.license_type = v_lictype;
  v_startmonth := EXtract(month from v_firstdate);
  v_startyear := EXtract(year from v_firstdate);
  v_endmonth := EXtract(month from v_enddate);
  v_endyear := EXtract(year from v_enddate);
  v_monthinx := v_startmonth;
  v_yearinx := v_startyear;
  while v_monthinx+(12*v_yearinx) <= v_endmonth+(12*v_endyear) LOOP
    SELECT count(*)
      into vv_new_apps
      FROM checklist_history,
           license
     WHERE checklist_history.license_id = license.license_id and
           license.profession_id = v_prof AND
           license.license_type = v_lictype AND
           extract(month from checklist_history.entry_date) = v_monthinx AND
           extract(year from checklist_history.entry_date) = v_yearinx and
           checklist_history.entry_date <= v_enddate;
    SELECT vv_new_apps + count(*)
      into vv_new_apps
      FROM checklist,
           license
     WHERE checklist.license_id = license.license_id and
           license.profession_id = v_prof AND
           license.license_type = v_lictype AND
           extract(month from checklist.entry_date) = v_monthinx AND
           extract(year from checklist.entry_date) = v_yearinx and
           checklist.entry_date <= v_enddate;

    INSERT INTO divcomm_act_report
      (rpt_month,rpt_year,new_apps)
      VALUES (v_monthinx,v_yearinx,vv_new_apps);
    IF v_monthinx < 12 THEN
      v_monthinx := v_monthinx+1;
    ELSE
      v_monthinx := 1;
      v_yearinx := v_yearinx + 1;
    END IF;
   END Loop;
END;

 

[synapsevampire]

Oracle SPs have always been handled in unique ways using Crystal, however I'm not sure if something has chenged with XI as I've not coded Oracle SPs with it yet.

I would suggest that you first switch to using the Oracle Native connectivity as it's faster. You'll see it listed as Oracle Server in the connectivity.

When using Oracle against SPs in the early versions of Crystal, one had to use the Crystal supplied ODBC driver, and check the Procedure Returns Results checkbox, and use a in/out refcursor:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

I couldn't find anything directly related to XI, so I'd go with the old methods as they should work in newer versions as well.

This may also be related:

http://support.businessobjects.com/library/kbase/articles/c2017108.asp

-k

 

[bmpsu]

Thanks for the direction. The documentation helped. With my lack of knowledge with Oracle SP's, I visited the Oracle forums for some help. The trick was using an in/out refcursor as you mentioned. I did play around with using a package as the documentation suggests, but this was not necessary. I was able to connect directly to the stored procedure, use my parameter, and display the correct data.

Thanks