how to develop reports from a stored procedures

[Rishi Singh]

Hi All,
Iam working on a web based ERP application wherein there is an urgent requirement of extensive MIS reports generation.I have written stored procedures and invoking the same through the SQLJ via a java programm.
I would appreciate if someone can throw some light as to how do i pass the resultSet of the stored procedure to the Oracle 6.1 Reports Engine
for report generation.
Thanks
Rishi
SCJP,SCWCD

[Beksy Kurian]

Rishi,
I believe you can use ref cursors to pass the resultsets to oracle reports. In the Object Navigator -> Database Objects, select the stored procedure you want to use.
Regards
Beksy

[Rishi Singh]

Hi Beksy,
Can u throw some more light on ref cursor with an example and how do we make use of the same from within Report Server.
Thanks in advance
Rishi

[Beksy Kurian]

I am not working with Oracle reports server currently. So let me give you a sample from one of the Oracle sites.
1. Created Table called bonus1.
Create table bonus1 (ename varchar2(50), job varchar2(20),sal number, comm number);
2. Create a database package as follows:
Create or replace package bonus_pkg1 as
TYPE b_cursor is REF CURSOR RETURN bonus1%ROWTYPE;
Procedure bonus_refcur(bonus_data IN OUT b_cursor);
End bonus_pkg1;
Create or replace package body bonus_pkg1 as
Procedure bonus_refcur(bonus_data IN OUT b_cursor) as
Begin
Open bonus_data for select ename, job, sal, comm from bonus1;
End;
End bonus_pkg1;
3. Create a new report.
In the Data Model, create a 'Ref Cursor Query' by clicking on the PL/SQL icon.
Use the following code in PL/SQL block:
Function QR_1RefCurDS return bonus_pkg1.b_cursor is
refcur bonus_pkg1.b_cursor;
Begin
bonus_pkg1 .bonus_refcur(refcur);
return refcur;
End;
3. Create the report layout by using the Report Wizard.
Hope it helps!
Regards
Beksy

[Rishi Singh]

Thanks Beksy,
One more thing i would like you to clarify,I believe the above thing you are suggesting to use with Oracle 9iReport Server .If its true plz tell me how do we download the evaluation copy or we need to procure a licensed one .Is it bundled with Oracle9i Database server or Oracle9i Application Server.
one more thing, I am preparing for my Oracle 9i OCA Track most likely to appear in near future may be within a couple of month.If you have STS Test Series for 1Z001 /1Z007 or for 1Z0031.Plz let me know we can exchange mutually or if you are ready to sell or if you know someone who is ready for excahnge or sell, plz let me know.
Regards
Rishi

[Beksy Kurian]

Rishi,
I do not think reports developer or server comes with 9ias.
The latest version of 9i Developer suite can be downloaded from the following site.
http://technet.oracle.com/software/products/ids/content.html
I do not have any of the STS softwares. I have seen numerous ads here for selling those softwares (Don't know whether it is legal to do so).
Good luck!
Regards
Beksy