Java Stored Procedure Vs PL/SQL Stored Procedure

Question

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)?

Answer 1

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…