Recently I have worked on a project that I assume could be solved by using Java stored procedure (more precisely some recieving/sending email package).
Аfter a while I've found that there are very few number of Java classes and Java stored procedures in my company's database. I asked DBA and he said that using Java stored procedures is prohibited because "It can easily put the server down".
I would be greateful if someone could expain me: is it really dangerous to use Java stored procedures that use external packages? Are there any pitfalls? Is using Java stored procedures not a common practice?
Best Answer
A well-written Java stored procedure is unlikely to "put the server down", however, Java routines might have some performance implications, because invoking them means that Oracle needs to start a Java virtual machine (JVM) process, transfer control to it, and wait for the Java code to terminate. Since this is still happening within the scope of a SQL transaction, concurrency might suffer. This is especially true when Java is used to synchronously perform some potentially long running activities that depend on remote resources, such as sending and receiving email.
You probably want to avoid using Java stored procedure if possible; consider using the
UTL_MAILOracle built-in package to handle email messages.