Calling Oracle Stored Procedures - a Solution

[spackers]

Hi all,

 

We've recently decided to switch from Crystal to JasperReports for our reporting solution but hit the old problem of Oracle stored procedures and resultsets.

 

It's not practical for us to use only SELECT queries and Brian Burridge's workaround using stored functions, while it does solve the problem, means rewriting a lot of code and seems cumbersome - so we've come up with an alternative solution.

 

It's an open source extension called OraclePlus that plugs into JasperReports (or any other Java app) and transparently makes any stored procedure act just like a normal query. It does this by deriving a new JDBC driver from the standard Oracle driver so that it can specify the "ref cursor" parameter and return the result set.

 

You can find OraclePlus and more details at:

 

http://www.zenesis.com/products/oracleplus

 

I hope it's useful!

 

Regards,

John.

[bklawans]

Thanks for the tip. There is also a project available in the "Developer Forge" section of this site that allows you to call an Oracle stored procedure natively from JasperReports.

 

Just curious, what drove the switch from Crystal?

 

-Barry

[spackers]

I came across the DeveloperForge project only today, ironically just after posting! My understanding of the DeveloperForge version though is that it doesn't work with iReports and requires a different setup (in code) to get it working (although that's not to say it's difficult to implement). We wanted something that would work everywhere, most especially with the designer.

 

Actually, the designer was something stopped us from switching away from Crystal years ago - it's intuitive and easy to give to business users, and until recently we didn't see that there was much of an alternative.

 

Technically, Crystal is really poor. The GUI can crash, the API changes without warning, each API has different functionality (eg Java API does not support stored procedures), the runtime accesses the database differently to the designer, drivers don't work properly (we have to use a third party Oracle ODBC driver), documentation is confusing, the list goes on. I even used to run the designer under a debugger so that I could step past access violations.

 

John

[bklawans]

spackers wrote:

I came across the DeveloperForge project only today, ironically just after posting! My understanding of the DeveloperForge version though is that it doesn't work with iReports and requires a different setup (in code) to get it working (although that's not to say it's difficult to implement). We wanted something that would work everywhere, most especially with the designer. John

 

The DeveloperForge project shouldn't require any code - just be sure to set the query languange correctly in the JRXML and copy the jasperreports.properties file from the sample. The lack of iReport support is a problem, but as I understand it that should be coming in 1.2.6 which should be out pretty soon.

 

Thanks for the info on Crytal - I haven't used it in over 10 years and was wondering how it had changed. Doesn't sound like for the better.

 

-Barry

[Code_Slave]

I'm confused as to why you do not put your procedure/function in a select?

 

functions & procedures can be queried just the same as a database table.

unless your oracle is really old, or you have used "out" params.

Post edited by: code_slave, at: 2006/09/04 23:50

[spackers]

Because it requires defining (and maintaining) concrete in/out types and then copying the columns over one-by-one, which is a pain to do (although not insurmountable), plus we have a large stock of procedures which already work through Crystal that we wanted to port easily.

 

John