Solved

ref cursor query

Posted on 2001-06-14
4
1,152 Views
Last Modified: 2010-08-05
I want to use ref cursor query in my report.I have created the function but how to assign the selected columns as a source to fileds in layout.I am mainly intersted in some kind of documentation/link.

0
Comment
Question by:kogantimu
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 6193931
Did you try to follow steps described in the Reports on-line help ? In the topic "ref cursor, query" there is "Creating a local query:  Ref Cursor Query tool" Related Topic there.
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6194213
Doc ID:  Note:1015634.102
Subject:  REPORT BASED ON STORED PROCEDURE WHICH RETURNS REF CURSOR
Type:  PROBLEM
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  24-JUN-1999
Last Revision Date:  16-AUG-1999
 


PROGLEM DESCRIPTION:
====================

How do you create a report based on a stored procedure which returns a
ref cursor?


PROBLEM EXPLANATION:
====================

A REF CURSOR is a datatype for cursor variables.  Like a cursor, a cursor
variable points to the current row in the result set of a multi-row query.
However, cursors differ from cursor variables the way constants differ from
variables.  Whereas a cursor is static, a cursor variable is dynamic, since
it is not tied to a specific query, therefore giving more flexibility.

Cursor variables are mainly used to pass query result sets between PL/SQL
stored subprograms and various clients.

Solution: CREATE PACKAGE PROCEDURE WHICH RETURNS REF CURSOR


SOLUTION DESCRIPTION:
=====================

In order to use REF CURSORS, you need to be running Reports version 3.0.5.11.
You will get the REP-0065 and REP-0200 errors if you are runnning the base
version of Reports 3.0 (3.0.5.8.0).  This has been logged as Bug 571641.


SOLUTION EXPLANATION:
=====================
 
1. Create a table called bonus.

     Create table bonus
     (ename    varchar2(50),
           job       varchar2(20),
           sal       number,
           comm      number);

2.  Create a database package as follows:

     Create or replace package bonus_pkg as
       TYPE b_cursor is REF CURSOR RETURN bonus%ROWTYPE;
       Procedure bonus_refcur(bonus_data IN OUT b_cursor);
     End bonus_pkg;


     Create or replace package body bonus_pkg as
       Procedure bonus_refcur(bonus_data IN OUT b_cursor) as
       Begin
           Open bonus_data for select ename, job, sal, comm from bonus;
       End;
     End bonus_pkg;

3.  Create a new report. In the Data Model, create a 'Ref Cursor Query' by
    clicking on the PL/SQL icon.  Write following code in PL/SQL block:

     Function QR_1RefCurDS return bonus_pkg.b_cursor is
          refcur bonus_pkg.b_cursor;
     Begin
         bonus_pkg.bonus_refcur(refcur);
            return refcur;
     End;

3. Create the report layout by using the Report Wizard.

0
 
LVL 5

Accepted Solution

by:
ORACLEtune earned 50 total points
ID: 6194219
Reports6i, try this:

1. Created Table called bonus1.
Create table bonus1 (ename varchar2(50), job varchar2(20),sal number, comm number);

2. Created 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. Created a new report.
In the Data Model, created a 'Ref Cursor Query' by clicking on the PL/SQL icon. Used 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. Created the report layout by using the Report Wizard.
0
 

Author Comment

by:kogantimu
ID: 6195303
hi,
i will try this now.i think it works.But i was looking for more documentation so that what are the exceptions.if possible please put document link.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now