Embedded SQL in Java classes

Hi,

I am currently working with BMP EJBs and Oracle 8.1.7. I am finding that the queries are growing larger and more complex with lots of bind variables. I am uncomfortable hiding this logic away in the Java because:

1) The prepared statements get messy with so many input parameters
2) Hides dependencies on database objects
3) Need to rebuild/redeploy EAR If SQL logic changes.

So I would prefer to move this logic out of the Java and back into the database. Now I could write a PL/SQL package to encapsulate this and return a ref cursor to the EJB (as multiple row result-set). I have also considered placing most logic in views and joining across same simplifying the Java end queries. However I suspect there may be a performance hit if I do not limit the results to what I actually want (as opposed to a big view).

I am wondering what other options exist? What is the usual best practice approach here?

Thanks,

James Received on Sat Mar 05 2005 - 04:30:43 CST