Issue Description
Customer has a requirement to create a query Input Control based on calling Oracle Stored Procedure.
How to achieve it?
Resolution
TIBCO JasperReports® Server does not support using Oracle Stored Procedure which returns ref_cursor as Input Controls datasource, out-of-the-box.
But it might be possible to achieve the requirements by creating a function in Oracle database which 'processes' cursor returned by stored procedure and returns data as a table.
Let's say, we have a stored procedure without IN parameters, like
create or replace procedure "AS_SP" (p_cursor OUT SYS_REFCURSOR) as begin open p_cursor for select distinct shipcountry from orders order by shipcountry; end AS_SP;
which returns list of countries from a table.
The idea is:
- Create a custom Oracle type
create or replace type myTable as table of VARCHAR2(100);
- Create a function which fetches cursor records and creates myTable object:
create or replace function as_function RETURN myTable
as
i int;
l_var VARCHAR2(100);
l_data myTable := myTable();
p_rc sys_refcursor;
begin
i:=0;
as_SP(p_rc);
loop
FETCH p_rc INTO l_var;
EXIT WHEN p_rc%NOTFOUND;
i:=i+1;
l_data.extend;
l_data(i) := l_var;
end loop;
return l_data;
end;
- Use that function to retrieve data for input control:
SELECT COLUMN_VALUE from TABLE(cast(as_function as myTable))
Solution tested with TIBCO JasperReports® Server v.6.4.0
Ref. Case 01578476
Recommended Comments