Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1203
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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