Report based on a temporary table

[amongalen]

Here is my situation

I got a stored procedure that prepares data for the raport and puts it into a global temporary table. What is important, this temp table is "ON COMMIT DELETE ROWS".

Then I got my main procedure that is called from the Jasper report. As a parameter it takes a ref_cursor. First, it calls the other procedure. Immediately after that the cursor is opened for data from my temp table.

I've tried to call that exact same procedure in SQL developer and it works just fine. However, when I use it in the Jasper report, there is no data returned.

I've done some testing and it seems that changing temp table to "ON COMMIT PRESERVE ROWS" makes it to work. However, I don't want to change the type of the table because some other things could depend on that table.

It seems to me that for some reason Jasper performs a commit just after the query (procedure call in this case) and before using data from the cursor so it gets removed from the cursor as well.

Is there some other solution? Am I missing something? Maybe the problem is somewhere else?

[reportdev]

Any GTT with "ON COMMIT DELETE ROWS" is not getting the next row from the dataset. 

I have tried changing the GTT with on commit preserve and it works. yes it is frustrating and thats how Jasper is working. 

I believe its not Jasper committing, I think its Jasper making the session Inactive once it is done fetching the rows.

I had to change some of the GTT's to perform this functionality... I can say one thing check for dependencies before changing the GTT. 

           select * from all_dependencies ad
           where ad.referenced_name = 'GTT_NAME' ;

[amongalen]

I've done some futher testing. I've tried to run the exact same report using JasperReports Library from Java.

When I launch the report with default connection properties it doesn't work (no data). However, setting "AutoCommit" to "false" seems to work.

Is it possible to set AutoCommit to false for a data adapter in JasperSoft Studio?

When configurating the data adapter there is a "Connection Properties" tab. What are the viable properties? I wasn't able to find any info anywhere.

[reportdev]

There is a property in the Jasper Config Reference, but not sure how to use it. http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.jdbc.holdability

I will be contacting the TIBCO support (create a ticket with them and see what they say). 

Tried in both the following ways

<property name="net.sf.jasperreports.jdbc.holdability.hold"/>

<property name="net.sf.jasperreports.jdbc.holdability" value="hold"/>

failed as below:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unable to get next record from result set.
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRException: Unable to get next record from result set.
    at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:145)
    at net.sf.jasperreports.engine.fill.JRFillDataset.advanceDataSource(JRFillDataset.java:1538)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1387)
    at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1366)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1147)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:113)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-08103: object no longer exists

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1073)
    at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:359)
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:263)
    at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:141)
    ... 8 more
 

[reportdev]

Quick update: 

TIBCO Support mentioned that the GTT's will work only with On Commit Preserve.

They are contacting the developers to see if any other options are available

 

[szaharia]

Properties available in "Connection Properties" tab are driver-specific. For instance, if you're using the OracleDriver class, try to set the 'autoCommit' connection property to false (see attachment).

[amongalen]

Interesting... I think I've found another bug. It is not possible to edit data adapter's properties inside of JasperSoft Studio. I mean using "DataAdapter Editor" to be precise. For some reason it doesn't let you save the changes if you haven't changed anything else.

What is more, it depends on how you've created the data adapter in the first place. If it is visible in the "project explorer"/workspace it doesn't work. But if it is in the "repository explorer" it seems to work just fine.

@shertage I've tried it earlier but for somehow changes weren't applied (because of the above mentioned bug). Now I've made sure the changes are saved and indeed it works.

Thanks for help.

ps. why there even are "two types" of data adapters? Ones in "project explorer", others in the "repository explorer".