Home » Developer & Programmer » Reports & Discoverer » REP-1285 (Report Builder 10.1.2.0.2)
REP-1285 [message #462955] Mon, 28 June 2010 18:31 Go to next message
marktoth
Messages: 3
Registered: June 2010
Junior Member
I have a stored procedure in 10g that I am trying to call from Report Builder 10.1.2.0.2

Below is the sproc
// declared in the package definition
type refCursorXX is ref cursor;

//from the package body
procedure READ_AUCTIONS(Out_Cursor out refCursorXX) as
begin
open out_cursor for
select auctionid, name || ' ' || year auctionname
from auction
order by auctionends desc;
end READ_AUCTIONS;

and the PL/SQL in Reports Builder:
function QR_1RefCurDS return pkg_auction_crux.refCursorXX is
refcur pkg_auction_crux.refCursorXX;
begin
pkg_auction_crux.READ_AUCTIONS(refcur);
return refcur;
end;

When I attempt to create a report w/ report wizard I get the following error
REP-1285 Query QR_1 has no select elements.

I have searched the internet and only found 2 hit mentioning this error and no solution.
Can someone please help

[Updated on: Mon, 28 June 2010 18:36]

Report message to a moderator

Re: REP-1285 [message #463129 is a reply to message #462955] Tue, 29 June 2010 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure you can create a report based on a stored procedure that returns a ref cursor.
Re: REP-1285 [message #463153 is a reply to message #463129] Tue, 29 June 2010 18:12 Go to previous messageGo to next message
marktoth
Messages: 3
Registered: June 2010
Junior Member
OK you can use stored proc from an oracle database in Reports Builder and here is the trick
in you oracle package definition you MUST define a type RECORD with the datatype you want the cursor to return and define the cursor
like this.

package pkg_auction_crux as

type auction_rec is RECORD
( auctionid number,
auctionname varchar(50));
type auction_cur is ref cursor RETURN auction_rec;

procedure READ_AUCTIONS2(Out_Cursor IN OUT auction_cur);
end pkg_auction_crux;

Then in the package body define your stored procedure
procedure READ_AUCTIONS2(Out_Cursor IN OUT auction_cur) AS
BEGIN
open out_cursor for
select auctionid, name || ' ' || year auctionname
from auction
order by auctionends desc;
END READ_AUCTIONS2;

Notice I am selecting the exact field IN ORDER into the record type i definded in the package definition by using the cursor

Then in Reports Builder
define a PL/SQL query as follows

function QR_1RefCurDS return pkg_auction_crux.auction_cur is
refcur pkg_auction_crux.auction_cur;
begin
pkg_auction_crux.READ_AUCTIONS2(refcur);
return refcur;
end;

Notice the return type of the procedure is the fully qualified name of the ref cursor you defined in you package definition

I declare a variable named refcur to be of that type
and then call the stored procedure passing it my defined refcur
then return it from the procedure

Problem Solved!
Razz

[Updated on: Tue, 29 June 2010 18:15]

Report message to a moderator

Re: REP-1285 [message #463155 is a reply to message #463153] Tue, 29 June 2010 19:11 Go to previous messageGo to next message
marktoth
Messages: 3
Registered: June 2010
Junior Member
Now we extend this out so that the sproc will take a user parameter passed to the report

first alter the sproc in oracle database to accept an input parameter
procedure READ_AUCTIONS2(Out_Cursor IN OUT auction_cur, IN_AUCTIONID IN NUMBER) AS
BEGIN
open out_cursor for
select auctionid, name || ' ' || year auctionname
from auction
WHERE AUCTIONID = IN_AUCTIONID
order by auctionends desc;
END READ_AUCTIONS2;




Then I define a user parameter in Reports Builder called IN_AUCTIONID with a type number

Then edit the PL/SQL statement in the above post to pass the user parameter to the stored procedure

function QR_1RefCurDS return pkg_auction_crux.auction_cur is
refcur pkg_auction_crux.auction_cur;
begin
pkg_auction_crux.READ_AUCTIONS2(refcur, :IN_AUCTIONID);
return refcur;
end;

Easy Squezzy!


[Updated on: Tue, 29 June 2010 19:12]

Report message to a moderator

Re: REP-1285 [message #463303 is a reply to message #463155] Wed, 30 June 2010 11:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Mark! It seems that today I learnt something new. I'll have to try it, once.
Re: REP-1285 [message #487217 is a reply to message #463303] Fri, 24 December 2010 00:37 Go to previous messageGo to next message
ernest
Messages: 1
Registered: December 2010
Location: India
Junior Member
Hello Mark!

I have a pagkage in oracle 10g which contains a function returning a ref cursor in Oracle 10g.

In the Oracle reports I have selected the 'ref cursor query' and created the required query.
The query is compiled perfectly.
But at run time it is generating the REP-1285 error.

Also there are no fields displayed in the data model.

Please give me some solution.

Thanks,

Erne
Re: REP-1285 [message #487224 is a reply to message #487217] Fri, 24 December 2010 01:26 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you did something wrong.

Oracle

REP-1285: Query '<query name>' has no select elements.

Cause: Your data model contains a query that does not select anything. Each query in your data model must select at least one element.

Action: Enter a SELECT statement in the query, selecting at least one element.
Previous Topic: Graph out put at report level
Next Topic: how to run report developer 10g
Goto Forum:
  


Current Time: Thu Mar 28 23:39:13 CDT 2024