Oracle – Java Stored Procedure Vs PL/SQL Stored Procedure

java-stored-proceduresoracleplsql

In Oracle DBMS, which is better performance, calling a java stored procedure from another pl/sql stored procedure or calling a pl/sql stored procedure from another pl/sql stored procedure.

By the way I have a loop in my pl/sql procedure that will call the java procedure multiple times (i.e. my code is flipping between PL/SQL and Java Stored Procedures), so does this slow down the performance)?

Best Answer

Any switch from one language to another will involve an overhead (it might be small but it'll still be there). If it's in a loop it will be accentuated.

Keep it simple and if you can stick to PL/SQL then do so.

Tom Kyte (Oracle Corporation Vice President and Guru) has a mantra which seems fitting to repeat here:

(Reference: http://tkyte.blogspot.com/2006/10/slow-by-slow.html)

  • You should do it in a single SQL statement if at all possible.
  • If you cannot do it in a single SQL Statement, then do it in PL/SQL.
  • If you cannot do it in PL/SQL, try a Java Stored Procedure.
  • If you cannot do it in Java, do it in a C external procedure.
  • If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
Related Topic