| Cannot call a function when creating a dataset [message #658777] |
Wed, 09 March 2011 13:28  |
Eclipse User |
|
|
|
Hello,
I ran into a problem I hope someone can help.
I created a function in Oracle 10g XE as such:
------
create or replace package GetEmployeeCursors is
type empResultSet is REF CURSOR;
function Funct1 return empResultSet;
end GetEmployeeCursors;
/
create or replace package body GetEmployeeCursors is function Funct1 return empResultSet is
tmpResultSet empResultSet;
begin
open tmpResultSet for
select * from employee;
return tmpResultSet;
end Funct1;
end GetEmployeeCursors;
/
------
The goal is simply to return a result set cursor so that I can query it when I create a new dataset in a BIRT report.
From the sqlplus command line, I am able to call function GetEmployeeCursors.Funct1 successfully in 2 different ways:
(1)
SQL> select GetEmployeeCursors.Funct1 FROM dual;
(2)
SQL> variable cursor_ret refcursor;
SQL> exec :cursor_ret := getEmployeecursors.funct1;
In creating a new dataset within BIRT, I set the Data Set Type to "SQL Select Query". On the Query screen, I entered:
select GetEmployeeCursors.Funct1
from FROM dual;
But I got the following error when I tried to preview the resutls:
A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.5.2.v20100209
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message:Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00903: invalid table name
If this works on the sqlplus command line, why doesn't it work within BIRT?
I thought the function returns a ResultSet object.
Can anyone pinpoint what should I be doing instead to call that function?
Thanks so much advance,
--Anna
|
|
|
|
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04290 seconds
Home
