3

Greetings everybody,

I came across the feature of using java in oracle, a few days back. Ever since I am wondering about the possibility of writing static methods in java replacing regular PL/SQL logic.

As I have reasonably adequate experience with java plus the rich libraries it offers, I am tempted to write java methods instead of regular PL/SQL. Would this be a good practice?. Will there be much performance overhead in doing so?. Thanks in advance.

komedit1
  • 255
  • 1
  • 7
  • 14

3 Answers3

10

There will be overheads involved in your decision to walk away from PL/SQL into Java code.

Although i am not a big believer of putting business logic into PL/SQL, i have seen just too many companies doing that, including my own.

The "performance consideration" of doing this does not come as whether or not to use static methods. For example, you may need to declare an arraylist to sort the values, and depending on the values, retrieve more results from the database in another query.

IMO, i would put business logic in my application, and not invest in PL/SQL. This also helps to ensure DBMS portability and not buy-in.

Oh Chin Boon
  • 23,028
  • 51
  • 143
  • 215
  • +2 for keeping logic in the application – Dapeng Jul 27 '11 at 09:07
  • 5
    Although, a counter-point is that if you have bought Oracle, you might as well use it. – Greg Reynolds Jul 27 '11 at 09:29
  • 1
    There is a good Q&A on logic in the DB or App layer here (Personally i'd enforce certain logic in the DB and some in App layer, even if it meant duplication): http://stackoverflow.com/questions/119540/business-logic-database-or-application-layer – Ollie Jul 27 '11 at 09:49
  • 3
    Not relevant for the OP question ! Your considerations are application design considerations. They are valid but, they don't answer the question. He wants to rewrites pl/sql procedure in Java, WITHIN ORACLE! – zep Jul 27 '11 at 13:09
6

It depends! They are two languages for different purposes!

What kind of operations do you want to do?

  • Every operations that touches data layer(I know it's debatable)
  • Massive Sql operations with large data processing( bulk operations)
  • To manage any kind of transaction
  • The job can be easily done in Pl/Sql
  • Are you writing code that is strictly tied to Sql data types?
  • Monitoring the relations between the code and dependent db objects

In this case, I believe Pl/SQL is the best choice for performance.

Attention, I wrote PL/SQL:

  • PL as procedure language(2nd choice)
  • SQL as data query language(1st choice)

The power of this two languages, strictly tied in Oracle, let you write application which access data in Oracle in faster and easier way than in any other language.

Do you need:

  • Operating system operations
  • Directory and file operations
  • Massive mails operations
  • Network operations
  • Other things you can't do in:
    • SQL (1st choice)
    • PL (2nd choice)
  • write code to reuse in architecture like Enterprise Java Beans
  • Write code to reuse elsewhere

In this case I think Java is a better choice, 3rd choice in Oracle.

But I'm not reinventing the wheel, this information are largely accessible and proved by Oracle experts like Tom Kyte etc.

Just some useful link to search:

zep
  • 1,121
  • 7
  • 14
2

I think it can be acceptable if you store the CRUD operations as stored procs. If you use any ORM framework, then you might map the SPs. It is more usual that the database is the more stable part of the application. The application might be refactored or replaced, but the database does not change (it is just my experience).

HamoriZ
  • 2,370
  • 18
  • 38
  • I agree, I've seen apps come and go (and all sorts of varying business logic), but databases tend to grow/evolve over time rather than getting scrapped and completely redone. in general, put data related build/maint logic on database side (incl views/mat views), and let app concern itself with display/usability issues. – tbone Jul 27 '11 at 16:00