Solved

ref cursor query

Posted on 2001-06-14
4
1,149 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

21 Experts available now in Live!

Get 1:1 Help Now