Native Java in Oracle vs PL/SQL (Why to use Java in Oracle?)
Joined: Jul 08, 2009
First of alll I am a database guy. I just came to know that we can you java code natively with Oracle. But I didn't get the situation, how to decide when to use native java in oracle and when to use pl/sql.
May I get any source, any real problem, through which I come to know, for which things I need to move on Java and C, while having PL/SQL. What are the limitations of PL/SQL that can be done only in Java or C.
Actually I need some test cases. With my little experience I am not able to think when I should move for Java and C natively.
I don't think there are things doable with Java you could not do with PL/SQL. I guess PL/SQL would be more performant even (some of the latest optimizations - eg. the automatic bulk collect - are certainly unavailable in Java). The reasons for Java in Oracle DB (in my opinion) are:
1) you have an existing Java code you want to move to the database to save network roundtrips and cannot afford (or don't want to) port them to PL/SQL,
2) you have a third-party Java library you want to use in your database-side operations (we once had Java code in the database to generate PDF files using an Apache FOP library),
3) you need to develop database side code, but you or your developers know Java much better than PL/SQL.
If you already know PL/SQL and none of these points applies to you, I doubt you would benefit at all from switching to Java. PL/SQL offers you compile-time check against your schema (although there is SQLJ built in the database, but who uses it?), unbeatable ease of using bind variables (SQLJ again) and optimizations not readily available to Java.
In any case, this documentation seems to be a good starting point. (Earlier versions of this documentation seem to suggest that Oracle introduced Java into the database to substitute an application server it didn't have at that time. After Oracle introduced the OAS, some of the related functionality was removed from DB Java and migration to OAS was offered as a solution.)