Oracle: Java stored procedure restriction

Question

I know that stored procedures in Oracle can be encoded in PL / SQL and Java.

May I find out what are the advantages and disadvantages.

I think I take certification for PL / SQL, but I believe that PL / SQL does not have a big advantage over Java SP.

Thirdly, I found that most of my developers are java pro-efficient, so it makes sense to develop SP in Java, so they don’t need to learn another language. (reduce development time and complications)

1) So should I stick with writing SP in Java?

Answer 1

I had some experience writing Java stored procedures, so I think I can definitely shed some light on this topic. Together with another lead developer, I ported Oracle Workflow - its Core Engine from PL / SQL to Java, WF Builder, and Runtime Viewer from VC ++ to Eclipse RCP stored procedures.

Why did we choose Java packages?

• In the future, the WF Engine may be migrated from the database to a dedicated application server.
• Workflows should start and respond to events. It is easy to execute using JMS.
• Oracle also provided a JMS implementation called AQs (Advanced Queuing).
  • AQs are supported by database tables. Thus, it was fairly easy to request event data for the user interface.
  • Access to AQs from Java stored procedures was quick and easy (no network overhead).

Problem problems

• JVM support has not been updated. We had to support Oracle 9, so he wrote the engine to work on JDK 1.3, and that was a pain in the neck. Oracle 10 switched to JDK 1.4 and I think that now everything looks much better with JDK 1.6 . I found out that support is still behind JDK 1.5 in Oracle 11.
• Sometimes you encounter situations when a trigger or pointer Ref, i.e. some kind of PL / SQL construct works best. If you must have your application exclusively in Java, you may need a workaround. Otherwise, you can implement this functionality in PL / SQL and call it from Java. We did not have this freedom.

Answer 2

In short:

Java on top of PL / SQL

• PL / SQL is a procedural language, Java is an object oriented
• There are many more third-party libraries in Java.
• PL / SQL functionality is limited, even with all additional libraries

PL / SQL over Java

• It is much easier to develop small and simple programs.
• It has more natural designs for processing data directly in tables.
• It is sometimes difficult to deploy third-party Java libraries, even developed by Oracle and using the manuals from its official website (it can work in one version of the database, but not in others)

But in most cases, PL / SQL is the preferred way to develop stored procedures. If you cannot solve the problem using PL / SQL, then this is the reason for finding a solution outside the scope of the stored procedures. First of all, OracleDB is a database, and it allows you to maintain the logic inside it as a bonus, but it is not an application server ... Oracle has Java application servers, by the way.

But sometimes you need a stored procedure / function, and the only way is to use fully functional languages ​​/ environments with extended functionality. And Java is the easiest way in such cases (for Oracle), much easier than development in C / C ++.