How can i use plsql object type as output parameter in BIRT report

Hi,

I have to call procedure and get PLSQL object type as output parameter in BIRT report. But in parameters section of the data set i am not getting any option to set that as parameter. How can i use object type into BIRT report. it will return me data in table format.

Thanks in advance,
Ashish

Find more posts tagged with

Comments

Hans_vd

Hi Ashish, 
 
BIRT cannot understand datatypes that you have defined in your database. 
 
The easiest way to get the data in BIRT is to create an output parameter of type sys_refcursor and in your pl/sql procedure open the refcur with a SELECT from the PL/SQL type. 
 
 
Take a look at this code: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>CREATE OR REPLACE TYPE object_type_test AS OBJECT (
col1 VARCHAR2(20),
col2 VARCHAR2(20)
);

CREATE OR REPLACE TYPE object_type_test_tab AS TABLE OF object_type_test;

CREATE OR REPLACE PROCEDURE refcur_object_type_test (p_refcur OUT SYS_REFCURSOR)
IS
l_object_type_test_tab object_type_test_tab := object_type_test_tab();
BEGIN
l_object_type_test_tab.extend (2);
l_object_type_test_tab(1) := object_type_test('value 1', 'value 2');
l_object_type_test_tab(2) := object_type_test('value 3', 'value 4');

OPEN p_refcur FOR
SELECT *
FROM TABLE (CAST (l_object_type_test_tab AS object_type_test_tab));
END;</pre>
 
 
Now you create a dataset of type "Stored procedure" and enter this call: 
{call OWNER_IV.refcur_object_type_test (?)} 
 
Bind an output parameter (keep the default datatype String) to the dataset and try "preview results" 
 
This should work 
 
Regards 
Hans