Why PL/SQL when you could use Java?

[Richard Boren]

I am currently learning Java and Oracle, and I have a question regarding the advantage of using PL/SQL for accessing the database over using a language like Java. I don't see the need for PL/SQL, a procedural language when I can embed SQL into Java, a object oriented language, and still get access to the database. My understanding is that SQL statements are always sent to the SQL statement processor residing on the Oracle server no matter how they were sent.

[Fredrik Safstrom]

Richard,
The argument to use PL/SQL in stead of Java would be performance and less network-traffic.
For example, to update table 1 with data from table 2 would mean that you read the data over the network to your client, do the processing of the data and then update the database again. If the tables contain a lot of data, this would cause a lot of network traffic.
In PL/SQL you could do this directly on the database server and would save some network traffic. You would also gain performance unless you do some complicated analysis of the data that your client is faster at doing than the server is.
/Bamse

[SoonAnn Lim]

I agree that Java can do good job interaction with database. I guess the issue is not which is better, it is which one costs less to use. If you have a system running on Java, then switching to PL/SQL is costly and vise versa. Business world.

[Richard Boren]

Originally posted by Fredrik Safstrom:
Richard,
The argument to use PL/SQL in stead of Java would be performance and less network-traffic.
For example, to update table 1 with data from table 2 would mean that you read the data over the network to your client, do the processing of the data and then update the database again. If the tables contain a lot of data, this would cause a lot of network traffic.
In PL/SQL you could do this directly on the database server and would save some network traffic. You would also gain performance unless you do some complicated analysis of the data that your client is faster at doing than the server is.
/Bamse

Fredrik, is this due to the fact that PL/SQL procdures can be stored on the server side?
Just to show my ignorance here. I understood java object could be stored on the server side as well. Wouldn't that also reduce the network traffic?

[Vishakha Ahuja]

Originally posted by Fredrik Safstrom:
Richard,
The argument to use PL/SQL in stead of Java would be performance and less network-traffic.
For example, to update table 1 with data from table 2 would mean that you read the data over the network to your client, do the processing of the data and then update the database again. If the tables contain a lot of data, this would cause a lot of network traffic.
In PL/SQL you could do this directly on the database server and would save some network traffic. You would also gain performance unless you do some complicated analysis of the data that your client is faster at doing than the server is.
/Bamse

Hi
I don't know much about PL/SQL, but if you use it for getting data on the client side, like Java, wouldn't it also increase the network traffic ?

[Fredrik Safstrom]

Richard,
Fredrik, is this due to the fact that PL/SQL procdures can be stored on the server side?
-> Yes, the PL/SQL is executed on the server. Actually, the Oracle server would execute this for you, so everything would be executed internally in the Oracle server. That is why you would gain performance.
On the other hand, if you are doing some complicated data-manipulation in the PL/SQL you might slow down the Oracle server for other cilents.
Just to show my ignorance here. I understood java object could be stored on the server side as well. Wouldn't that also reduce the network traffic?
-> If you would run the Java client on the same machine as your Oracle server, you would of course not have any network traffic.
/Bamse

[Armand Aquino]

PL/SQL is native to oracle so it is faster. Oracle is one of the biggest supporters of java and will make it faster (my friend at oracle is working on that as i write this). But what i don't like about calling pl/sql procedures with a callable statement, is it's harder to debug, harder to maintain, and your application just became vendor specific. If you switch databases, then normally all you have to do is change parameters for the JDBC call. What also sucks is when new logic has to be implemented, testing and debugging becomes even more of a pain.
I would use PL/SQL if i where a DBA or an application developer using oracle forms and reports.

[Joshua White]

Oracle 8i introduces java stored procedures and java remote procedures. Check it out at technet.oracle.com. Very interesting reading.
------------------
Joshua White
Pursuing Java Certification
[This message has been edited by Joshua White (edited August 16, 2001).]

[Richard Boren]

Originally posted by Joshua White:
Oracle 8i introduces java stored procedures and java remote procedures. Check it out at technet.oracle.com. Very interesting reading.

The doc Joshua is refering to is here.
Thanks Joshua, good stuff.
[This message has been edited by Richard Boren (edited August 16, 2001).]
[This message has been edited by Richard Boren (edited August 16, 2001).]