Re: Stored Procedure and Crystal Reports

Alex wrote:

> 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;
> /

Crystal Reports (it is well documented on their web site) requires a stored procedure, not a function, and that the REF CURSOR parameter be IN OUT even though you pass nothing in.

Daniel Morgan Received on Sun Jan 12 2003 - 23:29:01 CET