Re: Oracle procedures for Crystal Reports

From: timkarnold <timkarnold_at_home.com>
Date: Tue, 25 Sep 2001 13:39:44 GMT
Message-ID: <AO%r7.2795$G71.856984_at_news1.rdc1.md.home.com>

ORA-01403 no data found

The information in the article refers to: Seagate Crystal Reports 6

Applies to:

Reported version only
Connecting to a Data source
Error message
Parameters

Synopsis

An attempt is being made to report off Oracle stored procedure that is calling function.

Error : ORA-01403 no data found occured.

Solution

The error "ORA-01403 no data found" is not an error in Oracle all it means is that all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

The error occures since Oracle stored procedure is returning number of rows and for Seagate Crystal Reports (SCR) this is an error.

Since Oracle does not have dynamic way to suppress return message from stored procedure you can not create report that is calling function or stored procedure from stored procedure.

However, if View is calling function and stored procedrue is created off View, SCR will work properly.

SAMPLE: FUNCTION: FUNCTION PROC2FUN (mynumber in number)
  RETURN varchar2 IS tmp varchar2(50);
BEGIN
if mynumber > 100 then

          tmp:='Number is greater then 100'; else

          tmp:='Number is less then 100'; end if;
return tmp;
end proc2fun;

VIEW: funview

select

fun.Firstname "First Name",
fun.lastname "Last Name",
fun.sales "Sales per Person",
fun.id "Identification",

substr(proc2fun(fun.sales),1,30) "Raise" from fun

PACKAGE AND PROCEDURE: PACKAGE cursor_types7 AS
TYPE CrystalCursor IS REF CURSOR RETURN FunView%ROWTYPE; END cursor_types7;

PROCEDURE Proc2(curs IN OUT cursor_types7.CrystalCursor,

                      id_less in number)

AS BEGIN OPEN curs
FOR SELECT * FROM FunView
where "Identification"<id_less;
END Proc2;

---

---

"inna" <mednyk_at_hotmail.com> wrote in message news:347a408b.0109211427.450fa00f_at_posting.google.com...
> How to create Oracle procedure for Crystal Report?
> Usually I am using Package and Package body And Ref cursor.
> Now as I understood that I can't use them anymore, so what should I do?
> there is ex. of what we are using:
>
> CREATE OR REPLACE PACKAGE PUSER.CURRQCURRHIST
> AS
> TYPE RCT1 IS REF CURSOR;
> PROCEDURE CURRQCURRHIST
> (P1 INTEGER,
> RC1 IN OUT RCT1);
> END;
> /
>
> CREATE OR REPLACE PACKAGE BODY PUSER.CURRQCURRHIST
> AS
> PROCEDURE CURRQCURRHIST
> (P1 INTEGER,
> RC1 IN OUT RCT1)
> AS
> BEGIN
> OPEN RC1 FOR
> SELECT TCurrRate.UNIQUEID, TCurrRate.RATE, TCurrRate.STARTDATE,
 TCurrRate.ENDDATE
> FROM TCurrRate
> WHERE (((TCurrRate.CurAssocid)=P1))
> ORDER BY TCurrRate.STARTDATE DESC, TCurrRate.ENDDATE DESC;
> END;
> END;
> /
> What is the solution for crystal reports for same procedure
> Thank you.
Received on Tue Sep 25 2001 - 15:39:44 CEST