• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

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.

0
kogantimu
Asked:
kogantimu
  • 2
1 Solution
 
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
 
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now