SP help 1

[bmpsu]

I am very new to writing Oracle Stored Procedures. I want to make sure my syntax is the problem or not the problem. I have visited another forum and got some insight as well as visited the ‘internet’ for some help. I eventually want this SP to run from a Crystal Report (XI). It seems that when I run this SP from Crystal the only time it executes correctly is when I initially run the SP and I’m prompted for database connection information or when I use the Crystal command – Verify Database which also prompts for database connection information. Any refresh attempt will not execute this SP. My only conclusion is that I am missing something in my SP to complete the transaction. Or my SP is correct and there is something beyond this forum I will need to research some more. Here is an example of my SP. I had to use Crystals OLE DB (ADO) connection because the application it will be used in requires it. On every refresh I get prompted for a date parameter like I should, but I don’t get the results I should.

ANY input/help is much appreciated. If this SP is written correctly, I will then explore other issues beyond this forum (unless someone has all the answers!!!)

CREATE OR REPLACE PROCEDURE
sp_divcomm_act_report (v_enddate IN 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 := 1000;
 v_dgcitem number := 1005;
 v_cgccitem number := 600;
 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;

I found some documentation that states I need a Package to use Oracle Stored Procedures in Crystal and I attempted to create one.

CREATE OR REPLACE PACKAGE dca_report AS 
   PROCEDURE sp_divcomm_act_report(v_enddate IN date); 
END dca_report;

 

[oradba101]

Hi, bmanning2

I use Crystal Reports as well. You should not need a package to use stored procedures. We have several reports based upon stored procedures and they work fine. We use versions 8.5 and 10.

Regards,


William Chadbourne
Oracle DBA

 

[bmpsu]

Thank you for your response. I initially created this report without using a package and still could not get this to work correctly. The idea of this SP was to populate at table with data every time it is ran from in the application. Do you have any insight into what I am doing wrong.

Thanks

 

[TheEntertainer]

bmanning2,

I to work with Crystal Reports XI and Oracle and my understanding is that for Crystal to read/see a recordset it has to be in a package.

You need to search for a document called cr_oracle_stored_procedures.pdf on business object website.

You should also look at this "thread759-1197042"

A couple of important points you need a package and a package body

Also the code in the package body needs to end with
OPEN Cursor FOR
SELECT *
FROM table


I hope this helps

TheEntertainer

 

[bmpsu]

I was able to get the results I needed. With a combination of information from the thread you posted and the Crystal Reports documentation, I was able to get results. I will post my example in case anyone is looking for a solution. I was hoping with XI, I wouldn't need a package and to use the Oracle Native connectivity. But It seems to have not changed from 9 and 10. The application I would need this report to run from seems to only work with OLE (DB) ADO connectivity.

CREATE OR REPLACE PACKAGE pkg_divcomm_act_report
 AS TYPE Test_Type IS REF CURSOR RETURN 
 divcomm_act_report%ROWTYPE;
 PROCEDURE sp_divcomm_act_report (
 divcomm_Cursor IN OUT Test_Type,
 v_enddate IN date
 );
 END pkg_divcomm_act_report;

REATE OR REPLACE PACKAGE BODY pkg_divcomm_act_report
AS
PROCEDURE sp_divcomm_act_report (divcomm_Cursor IN OUT Test_Type, v_enddate IN date)

IS
BEGIN

DECLARE
 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 := 1000;
 v_dgcitem number := 1005;
 v_cgccitem number := 600;
 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; 
  
  OPEN divcomm_Cursor FOR
  select * from divcomm_act_report;

END sp_divcomm_act_report;
END pkg_divcomm_act_report;
/

Then to test:

declare
 divcomm_Cursor pkg_divcomm_act_report.test_type;
 resultset divcomm_Cursor%rowtype;
 begin
 
pkg_divcomm_act_report.sp_divcomm_act_report(divcomm_Cursor, '11-MAY-06');
 if not divcomm_Cursor%isopen then
 dbms_output.put_line('the cursor is not open');
 else
 dbms_output.put_line('the cursor is open');
 end if;
 fetch divcomm_Cursor into resultset;
 while divcomm_Cursor%found loop
 dbms_output.put_line(resultset.rpt_month);
 fetch divcomm_Cursor into resultset;
 end loop;
 end;

 

[bmpsu]

and I forgot...

Thanks for guiding me in the right direction. I have something that works and thats half the battle.

 

[Turkbear]

Hi,
To use a Stored Procedure in a Crystal Report ( including XI Patched to R2) there is no need for a Package, the SP can be created and used directly.
The REFCURSOR OUT Parameter is required however..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[bmpsu]

So it seems I may need a version update and/or service packs installed?

 

[Turkbear]

Hi,
Not sure about that. but, to confirm, this SP ( no Package involved) fine as a source for a Crystal Report ( even though it is a little complex):

(
 p_auditid IN VARCHAR2,
  p_return_cur OUT SYS_REFCURSOR,
  p_startDate IN  VARCHAR2,
  p_endDate IN VARCHAR2
 )
IS
BEGIN
  OPEN p_return_cur FOR
  SELECT "EATS_AGREEMENTS"."VENDOR_NAME", 
"EATS_AGREEMENTS"."AGREEMENT", "EATS_AGREEMENTS"."WORK_ORDER", 
"EATS_AGREEMENTS"."MASTER_AGR_YN", "AUDITOR_LIST"."EMPL_NM", 
"EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK", "EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED", 
"EATS_AUDITOR_WORK_VIEW"."STATUS_FROM", "EATS_AUDITOR_WORK_VIEW"."STATUS_TO"
 FROM   "EATS"."EATS_AGREEMENTS" "EATS_AGREEMENTS", "EATS"."EATS_AUDITOR_WORK_VIEW" "EATS_AUDITOR_WORK_VIEW", 
 "EATS"."AUDITOR_LIST" "AUDITOR_LIST"
 WHERE  
 ("EATS_AGREEMENTS"."RELATING_ID"="EATS_AUDITOR_WORK_VIEW"."RELATING_ID_PK" (+)) 
 AND ("EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK"="AUDITOR_LIST"."EMPL_NBR" (+)) 
 AND "EATS_AUDITOR_WORK_VIEW"."CHANGING_EMP_ID_FK" IN (select * from THE ( select cast(eats.in_list(p_auditid) as eats.StdTableType ) from dual))  AND 
 ("EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED" >= to_date(p_startDate,'MM/DD/YYYY') ) AND 
 ("EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED"< to_date(p_endDate,'MM/DD/YYYY') ) AND 
 "EATS_AGREEMENTS"."MASTER_AGR_YN"='N'
 ORDER BY "AUDITOR_LIST"."EMPL_NM", "EATS_AUDITOR_WORK_VIEW"."DATE_CHANGED";
END;

Note : the in_list call within the proc is from the asktom site regarding passing an IN list of values to a SP.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[bmpsu]

AWWW!
Thanks Turkbear.

I was able to accomplish everything I needed to without using Oracle Native connectivity or creating a package.

Works perfect.
Thanks.