ref cursor query

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.

kogantimuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Helena Markováprogrammer-analystCommented:
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
 
ORACLEtuneCommented:
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
 
ORACLEtuneCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
kogantimuAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.