Re: Stored procedure and Oracle

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

1 Do away with the temporary table, forget you sqlserver habits. In Oracle you don't need temporary tables, and if you think you need them you should use globally temporary tables. (They are dropped automatically once you leave the procedure)
2 You'll need a *weak* cursor, so you have to leave out the 'return... ' part
3 In sql*plus you can do the following
variable rc refcursor
<call your procedure>
print rc

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address