aspose file tools*
The moose likes Oracle/OAS and the fly likes how to develop reports from a stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "how to develop reports from a stored procedures" Watch "how to develop reports from a stored procedures" New topic
Author

how to develop reports from a stored procedures

Rishi Singh
Ranch Hand

Joined: Dec 09, 2000
Posts: 321
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
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
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
Ranch Hand

Joined: Dec 09, 2000
Posts: 321
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
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
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
Ranch Hand

Joined: Dec 09, 2000
Posts: 321
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
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to develop reports from a stored procedures
 
Similar Threads
How to handle stored procedures
Java & Oracle Stored Procedures
AJAX In Practice: implementing pagination?
Stored procedures in J2EE
How resultset is returned by stored procedure?