• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

how to develop reports from a stored procedures

 
Ranch Hand
Posts: 321
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 321
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 321
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic