| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Stored procedure and Oracle
Sybrand Bakker wrote:
> "Alex" <malex_at_woh.rr.com> wrote in message
> news:ffkU9.95831$%3.24795029_at_twister.neo.rr.com...
> > 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 requires that REF CURSORS be in procs and be IN OUT.
Daniel Morgan Received on Sun Jan 12 2003 - 16:46:10 CST
 |
 |