| REP-1285 [message #462955] |
Mon, 28 June 2010 18:31  |
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 #463153 is a reply to message #463129] |
Tue, 29 June 2010 18:12  |
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!
[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 |
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 #487224 is a reply to message #487217] |
Fri, 24 December 2010 01:26 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
