Jump to content
Changes to the Jaspersoft community edition download ×

Calling Oracle Stored Procedures - a Solution


spackers

Recommended Posts

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.

Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...