can please write the steps we have to take care for writing complex stored procedures in oracle but it should work even if we change the database to MySQL .
I think if future there will be a common api for all databases which can have same syntax for all sql queries..
As of now, if you want your app to be database independent, you should avoid stored procedures. I've been reading about Java stored procedures where the languages isn't PL/SQL. I don't know how common support is now though.
I'd question if you really need a stored procedure though. Many complex queries can be written using JDBC and remain database independent.
Thanks for the reply
1)in our existing application we already have some of the stored procedures written in oracle and we are planning to write some more stored procedure to parse large xml files and insert data in the oracle database which enhances the existing functionality of our core application.Also need the flexibility to migrate to mysql or db2.
i dont know may be we have to write complex queries in using JDBC and remain database independent as suggested by you avoiding stored procedure.
2)Do you have any idea about any R and D developments in progress to come up with common api which will be same syntax and same queries for all for any database.
3)As per my understanding hibernate will generate the sql queries based on the back end database so will it change the sql queries while generating queries based on the back end database.
i know how to call a stored procedure by using hibernate but i am not sure if we develop stored procedure in hibernate by using session createSQLQuery or named query and can we see the difference of generated queries by changing the database names?
Jeanne Boyarsky wrote: I've been reading about Java stored procedures where the languages isn't PL/SQL. I don't know how common support is now though.
As far as I know, support for Java inside the database is specific to Oracle database. It therefore won't make the code more portable at all, but it might make it more manageable by a Java developer. Or easier to move existing JDBC Code with minimal effort to the database to minimize network roundtrips.