CODEWITHSUNDEEP
javahibernatejpaplsql
Jigar Shah
Jigar Shah

Reputation: 2654

In what type of application or scenario PL/SQL is better as back-end?

I am a java/j2ee developer. I always like JPA/JTA or hibernate for ORM. Since it gives me portability. But for large scale application, portability is not that important sometimes. Lots of time they ask to use PL/SQL as BE. I always find it un necessary. Apart from ARRAY and scheduling etc. Cos, with PL/SQL, application logic gets fragmented in java and PLSQL.

What are business / application scenario where PLSQL is better in-terms of performance / design / maintainability.

Upvotes: 2

Views: 2555

Answers (2)

dimitrisli
dimitrisli

Reputation: 21401

Put it simply PL/SQL is the closer you can get to your Oracle DB therefore performance comes first in mind in situations where you are dealing with loads of database entries.

Upvotes: 1

duffymo
duffymo

Reputation: 308938

The answer to this usually depends on whether you prefer objects or relational databases.

A DBA would argue that middle tier applications come and go, but relational databases live forever. Portability is rarely important for relational databases, especially if a firm has made a large investment in Oracle. A decision to migrate to another vendor won't be made lightly.

A DBA may prefer stored procedures because it acts like a Java interface and shields users from the underlying schema details. S/he can modify the schema as will as long as the stored proc parameters don't change.

Sometimes stored procedures make sense for performance reasons. Why query for a large data set on the middle tier, process it, and put it back in the database when you can do all the calculations on the database server?

Using stored procs does force you to do maintenance on both middle tier and server, but that's a choice.

I don't believe there's a hard and fast answer that would say objects or stored procedures are always right. If that's what you're looking for, I'm afraid you're going to be disappointed.

Upvotes: 7

Related Questions