Stored Proc in Birt Report

Hello All,

I am using Birt Eclipse Designer for Birt Reporting and the backend is Oracle 9i.

I created a following package in oracle
=================================
create or replace package types
as
type cursorType is ref cursor;
end;
/
=================================
/*and a procedure */
=================================

create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
open p_cursor for select ename, empno from emp order by ename;
end;

=================================
the above package and procedure runs fine in oracle..even i am getting a result set ...

but when i try to use the same in Birt report designer it fails....
following is the way i am using procedure in eclipse birt

1.I create a new report
2. establish a DataSource Connection
3. Create new Data Set
4. and i call my procedure in following way in Query tab

call SCOTT.GETEMPS(' ')

but it throws an error msg
"can not get the resultset meta data
SQL statement does not return a resultset object"

does anyone have any clue why is it not working in Birt environment.

Thanks in advance

Find more posts tagged with

Comments

rpolunsky

I have a report somewhere that will do this. I'll look for it.

rpolunsky

I can find the report but not the database it was written against.

The report invocation is:

<property name="queryText">{call CHANGE_ANALYSIS2(?,?,?)}</property>

The query source is:

CREATE OR REPLACE PROCEDURE change_analysis2 (
refdate_mmddyyyy IN VARCHAR2,
numdays IN NUMBER,
changeset OUT sys_refcursor
)
AS
...
CLOSE cursor_data;
OPEN changeset FOR
SELECT *
FROM TABLE (CAST (chg_records AS chg_table));
END;

You might not need an IN OUT param.
sys_refcursor depends on your version of Oracle.

I hope this helps.