JDBC connection with Oracle Failing

RiF

I have been developing BIRT reports for an MRP system. I am using BIRT designer 4.2.2, and the Oracle 11g JDBC files.<br />
<br />
As a simple test, I am trying to perform a listagg and pivot operation using Oracle, and reporting in BIRT. I cannot use the BIRT pivot tables, as OOTB it does not handle string agregations. My sample data is taken from the HR user/tablespace of Oracle 11g XE.<br />
<br />
The query being used is:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT *
FROM
(SELECT Departments.Manager_Id,
job_id,
department_name
FROM Employees,
Departments
WHERE Employees.Department_Id = Departments.Department_Id
) pivot(listagg(job_id,', ')
within GROUP (
ORDER BY job_id) FOR department_name IN (
'Administration' AS Admin,
'Marketing' AS marketing,
'Purchasing' AS purchasing,
'Shipping' AS Shipping)
)
</pre>
<br />
Which successfully executes in Oracle SQL Developer.<br />
<br />
But when tested in BIRT, on using Preview Results, I get a 'Java heap Space' error.<br />
<br />
Decreasing the SQL to <br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT *
FROM
(SELECT Departments.Manager_Id,
job_id,
department_name
FROM Employees,
Departments
WHERE Employees.Department_Id = Departments.Department_Id
) pivot(listagg(job_id,', ') within GROUP (
ORDER BY job_id) FOR department_name IN (
'Marketing' AS marketing,
'Purchasing' AS purchasing,
'Shipping' AS Shipping)
)
</pre>
<br />
Successfully previews. Removing any one of the PIVOT groups successfully executes. But with all four (or more) does not execute.<br />
<br />
The problem seems to be that Oracle is producing very wide fields, which the JDBC connector cannot handle.<br />
<br />
Can you please advise a suitable solution??<br />
<br />
Thanks in advance.

kclark

Can you post the full error please?