no you can not write Java Stored Proceedure for SQL Server. The reason is SQL Server does not support Java as Oracle did.
Regards, Imran Mirza
posted 15 years ago
Environment - Application is getting developed in JAVA and backend database is Oracle 9i.
Query - For doing the Batch processing in the mentioned environment two options are available, namely 'Java Stored Procedures' OR 'Oracle Stored Procedures'. From Maintenance and Performance point which option should be opted for and Why. We need the some technical write-up to support the same.
posted 15 years ago
Can i deploy the same Java Stored Procedure across various databases or there is some majore change required?
How is your application going to pass data to the backend for batch processing ?
I personally prefer PL/SQL in Oracle over java stored procs. PL/SQL unlike Java is tightly integrated with SQL and was developed for that sole purpose. If DB independence is not a requirement... you will benefit from SP's. Unless you are doing one insert / update from java client at a time you won't see any benefit over SPs. All SQL statements fired from Java are dynamic by nature. PL/SQL can take advantage of explicit cursors where appropriate. Plus if you are planning to invoke your procedure from Client (middleTier.. lets say using execute batch) then calling a Oracle Stored Proc will be advantegeous.. as there wont be context switch from PL/SQL to Java for every statement you execute. From maintenance point of view - if DBA's are going to maintain it then , I think they will like simple PL/SQL. You dont need to build SQL statememnts in Strings dynamically. SQL in JDBC code isn't really readable compared to PL/SQL.
Though one cannot generalize any rules - 1. Use PL/SQL for SQL intensive stuff 2. Use java where PL/SQL isnt the natural choice - Sending Email, FTP, getting access to OS related things. 3. If you cant do it in Java then use C external routine.
Thats what we follow in our projects.
With 9iR2 you can also natively compile your SP in oracle if you are doing computive intensive tasks.