Stored Procedure and Crystal Reports

From: Alex <malex_at_woh.rr.com>
Date: Sun, 12 Jan 2003 20:31:35 GMT
Message-ID: <HgkU9.95846$%3.24796721_at_twister.neo.rr.com>

[Quoted] Help Please! I am new at Oracle, so you can be as elementary as you wish.

I am trying to use a PL/SQL stored procedure as the source of a Crystal report, but I get an error
message indication that the stored procedure has no records. I am new at Oracle and am modeling
the procedure after existing code. These are the steps I follow:

1. Create a Cursor reference variable in a package
2. Retrieve field values from various tables to variables, looping through each row.
3. Proces each iteration of the loop, inserting the values stored in variables to an intermediate or temporary table.
4. After the loop ends, retrieve all rows from the temporary table using the OPEN referral FOR SELECT * FROM CRYSTAL_AVGTIMEFILE_DATA
5. It is the active set from step 4 that I wish to pass to the stored procedure.

Everything works fine up to the point where I insert records into the temporary table, CRYSTAL_AVGTIMEFILE_DATA. The are successfully inserted into the table. The cursor variable is an IN OUT parameter for the procedure.
When I try running it from the SQL*Plus command line I get an error indication wrong number or type of parameters. I do not know how to supply the IN portion of the parameter from the command line since the type is REF cursor. The
existing Crystal reports have a corresponding referral parameter with a default of 1, and instruct the user to ignore the parameter. So I don't understand how the report is supplying the IN parameter to the procedure, nor how it is receivinf the out portion of that same parameter. my code follow:

This is the package definition:

    CREATE package CRYSTAL_AVGTIMFILE_PKG as

         type CmCur is REF CURSOR return CRYSTAL_AVGTIMFILE_DATA%ROWTYPE;     END CRYSTAL_AVGTIMFILE_PKG; HERE IS THE error message:

    ERROR at line 1:

    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'MY_PROC'
    ORA-06550: line 1, column 7:

    PL/SQL: Statement ignored

HERE IS THE procedure definition:

/* This procedure returns the latest entry for each terminated civil case. This is accomplished by creating and iterating through a cursor consisting of civil cases from the REAL_CASE table. For each case_Id returned, the DSP table is queried for the highest sequence number, or max(seq) to identify the latest entry for that case. The case_id and sequence number identified above is subsequenly used as the selection criteria to retrieve other required fields from the REAL_CASE, ACTNCD, and DSP tables. To ensure that only terminated cases are returned, this retrieval is performed with a conditional statement that returns only rows where the termination date
(scr_term_dt) is not NULL.

*/

CREATE OR REPLACE PROCEDURE my_proc

(Referral in out CRYSTAL_AVGTIMFILE_PKG.CmCur)

AS

    max_seq NUMBER (12);

    today Date;

    usrname VARCHAR2(20);
    var_case_id CHAR(20);
    var_case_cd CHAR(10);
    var_dscr VARCHAR2(50);
    var_actn_cd CHAR(20);
    var_actncd_dscr VARCHAR2(100);
    var_scr_cd dsp.scr_cd%TYPE;
    var_dsp_dt date;
    var_scr_dt date;
    var_scr_term_dt date;
    var_stat_dt date;
    var_file_dt date;
    var_dsp_cd CHAR(10);
    var_scr_term_cd char(10);

    var_term_cd CHAR(10);
    var_stat_cd CHAR(10);

BEGIN     SELECT user, sysdate
    INTO usrname, today
    FROM dual;

    REFRESH := 'YES';     If refresh = 'YES' then

         delete from CRYSTAL_AVGTIMFILE_DATA where user = usrname;     End IF;

    for c1rec in (SELECT case_id, dscr, actn_cd, case_cd from real_case

         WHERE case_cd in ('CV')
         AND case_id BETWEEN '6030' AND '6040'
         and case_cd is not NULL)

    LOOP
    BEGIN

     SELECT max(seq)
     INTO max_seq
         FROM DSP
     WHERE case_id = c1rec.case_id
     ORDER BY case_id;

     SELECT
     scr_dt, scr_cd, scr_term_dt, stat_dt, dsp_dt, dsp_cd, scr_term_cd,
term_cd, stat_cd
     INTO var_scr_dt, var_scr_cd, var_scr_term_dt, var_stat_dt, var_dsp_dt,
var_dsp_cd,
         var_scr_term_cd, var_term_cd, var_stat_cd
     FROM dsp
     WHERE case_id = c1rec.case_id
     AND SEQ = max_seq;

IF var_scr_term_dt is not null then
      var_dscr := c1rec.dscr;
      var_case_id := c1rec.case_id;
      var_case_cd := c1rec.case_cd;
      var_actn_cd := c1rec.actn_cd;

      SELECT file_dt
      INTO var_file_dt
      FROM REAL_CASE
      WHERE case_id = c1rec.case_id;

      SELECT dscr
      INTO var_actncd_dscr
      FROM actncd
      WHERE actn_cd = var_actn_cd;

     INSERT INTO CRYSTAL_AVGTIMFILE_DATA values
     (usrname, var_case_id, max_seq, var_case_cd, var_dscr, var_scr_dt,
var_file_dt,        var_scr_cd, var_scr_term_dt, var_stat_dt, var_dsp_dt,
var_actn_cd, var_actncd_dscr,      var_dsp_cd,

var_scr_term_cd, var_term_cd, var_stat_cd); End if;

    END;
    END LOOP;      open Referral for select * from CRYSTAL_AVGTIMFILE_DATA where user = usrname;

END my_proc;
/ Received on Sun Jan 12 2003 - 21:31:35 CET