Hi all,

I'm trying to figure out how to do the following thing. We have implemented new search functions with Lucene and Hibernate. Our search query is very complicated and is generated by Hibernate with lot of detached criterias, etc. When we use paging (display up to XX results on one page), it works pretty well. But when we to want to export search result (thousands of rows) into CSV file (or any other format), it takes ages and eats > 1GB of memory. This is not acceptable and is probably wrong approach. The problem is, that we can't change it to named queries or queries generated by our application (due to complicated environment and time).

So, I have an idea how to speed up this export stuff. I wrote oracle function CREATE_CSV, which has several arguments and of these arguments is native_sql_query. This function calls native_sql_query, creates CSV file internally and returns it. So far it works.

But now, I have no idea if I can tell Hibernate to pass Oracle native SQL query as an argument of my Oracle function. Something like this ...

From ->
SELECT * FROM blahblah WHERE blahblah (lot of joins, ...)

To ->
SELECT CREATE_CSV( "...", "SELECT * FROM blahblah WHERE blahblah (lot of joins, ...)", ...)

... so, Hibernate will not return rows with data, but CLOB with CSV file.

Is this possible? Or is this wrong approach with Hibernate?

Obviously I found solution when I sent new topic to this forum :(

1nd way - own dialect and modify Dialect.transformSelectString
2nd way - onPrepareStatement in Interceptor

Obviously I found solution when I sent new topic to this forum :(

1nd way - own dialect and modify Dialect.transformSelectString
2nd way - onPrepareStatement in Interceptor

Obviously I found solution when I sent new topic to this forum :(

1nd way - own dialect and modify Dialect.transformSelectString
2nd way - onPrepareStatement in Interceptor