<p>PL/SQL is not my native tongue. Oracle supports writing stored procedures in Java. What are the advantages of doing this over writing the stored procedures in PL/SQL</p>

[{'answer_id': 70136, 'author': 'Telcontar', 'author_id': 518, 'author_profile': 'https://Stackoverflow.com/users/518', 'pm_score': 1, 'selected': False, 'text': "<p>I have used Oracle emmbedded java for two problems:</p>\n\n<p>1) To do a PLSQL procedure which bulks the results of a query in a text file and send it over FTP. This file was very large and i use Java to Zip it.</p>\n\n<p>2) In a client-server aplication with direct connection with the DB, to compare the user sent password to the application (not the DB user password) hashed with MD5, so that the password not travel by the net in plain text. I'm not sure if this was the better solution for this problem, i'm going to ask it now. :)</p>\n"}, {'answer_id': 70551, 'author': 'Steve Bosman', 'author_id': 4389, 'author_profile': 'https://Stackoverflow.com/users/4389', 'pm_score': 1, 'selected': False, 'text': '<p><strong>Advantages:</strong></p>\n\n<ul>\n<li>Can share identical application logic in client and database</li>\n<li>Access to the Java API. Watch out for which java version each database supports - I believe 10g only supports 1.4 (which means at my work we have to be very careful since our main codebase has recently moved to 1.5).</li>\n</ul>\n\n<p><strong>Disadvantages:</strong></p>\n\n<ul>\n<li>Java stored procedures doing lots of database access can be quite slow</li>\n<li>Harder to deploy your code</li>\n</ul>\n'}, {'answer_id': 70676, 'author': 'Rob Paterson', 'author_id': 11571, 'author_profile': 'https://Stackoverflow.com/users/11571', 'pm_score': 4, 'selected': False, 'text': '<p>In the Oracle world the general order of development should be:</p>\n\n<p>Wherever possible do it purely with SQL.\nIf you need more than SQL do it with PL/SQL.\nIf you need something that PL/SQL can\'t do, then use Java.\nIf all else fails use C.\nIf you can\'t do it with C, back slowly away from the problem....</p>\n\n<p>PL/SQL stored procedures are an excellent way of moving your business logic to a layer that will be accessible by any integration technology. Business Logic in a Package (don\'t write stand alone Functions and Procedures - they\'ll grow over time in an unmanageable way) can be executed by Java, C#, PL/SQL, ODBC and so on.</p>\n\n<p>PL/SQL is the fastest way to throw around huge chunks of data outside of pure SQL. The "Bulk Binding" features means it works very well with the SQL engine.</p>\n\n<p>Java stored procedures are best for creating functionality that interacts with network or operating system. Examples would be, sending emails, FTP\'ing data, outputting to text files and zipping it up, executing host command lines in general.</p>\n\n<p>I\'ve never had to code up any C when working with Oracle, but presumably it could be used for integrating with legacy apps.</p>\n'}, {'answer_id': 70679, 'author': 'dogbane', 'author_id': 7412, 'author_profile': 'https://Stackoverflow.com/users/7412', 'pm_score': 2, 'selected': False, 'text': "<p>Java makes it possible to write database-neutral code. It allows you to reuse existing code and dramatically increase productivity.</p>\n\n<p>One thing I find Java Stored Procedures useful for is File IO. Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on, as compared to Oracle's UTL_FILE package.</p>\n"}, {'answer_id': 71372, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 4, 'selected': False, 'text': '<p>Only when you can\'t do it in PL/SQL ( or PL/SQL proves to be too slow, which would be pretty rare I believe ).</p>\n\n<p>As a case study... We had a single java stored procedure running in production ( Oracle 9i ), it was originally written in java because at the time we thought java was cool, Something I\'ve long since changed my mind about. Anyway. One day, the DB crashes, after it reboots the java SP doesn\'t work. After much back and forth with oracle support, they don\'t really know what the problem is and the only suggestions they have involve much downtime. Something which wasn\'t an option. 30 minutes later I had rewritten the java SP in PL/SQL.</p>\n\n<p>It now, runs faster, is oracle "native" , shares the same deployment process as other objects and is easier to debug.</p>\n\n<p>PL/SQL is a very capable language. If you are writing Stored Procedures, please take the time to learn it rather than just doing things in java because thats what you know.</p>\n'}, {'answer_id': 77566, 'author': 'darreljnz', 'author_id': 10538, 'author_profile': 'https://Stackoverflow.com/users/10538', 'pm_score': 2, 'selected': False, 'text': "<p>The main advantage is access to the API's and language features not found in PL/SQL. For example, I have used them for regular expression processing, file/directory manipulation and XML parsing.</p>\n\n<p>There are a number of disadvantages:</p>\n\n<ul>\n<li>Poor tool support\n<li>Lack of control over the JVM\n<li>DBA's often aren't trained in Java. In order to support your production code you'll either either need to give your DBA's more training or hire Java-trained support staff\n</ul>\n\n<p>Moving the Java to an application server is often a better approach as this counteracts the disadvantages. There is excellent tool support, great control over the JVM and there are heaps of people trained up in the popular application servers so finding support staff is easy. There is the opportunity cost of the performance hit moving away from the database but keeping Java close to the database doesn't give you great performance gains anyway.</p>\n\n<p>You definitely need a reason to use Java in the database over a) PL/SQL stored procedures or b) Java outside the database.</p>\n"}, {'answer_id': 85385, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>The answer is <em>NEVER</em>.  If you need to write programs to load or process data you need to do it outside of your data tier from another computer on the network.</p>\n\n<p>Running external applications directly on your data tier or god forbid in-process with your data tier or the misapplication of external languages when native query languages are a better fit for the job at hand are fine and perfectly acceptable for a small scale custom in-house application.  They simply have no place outside of that arena.</p>\n'}, {'answer_id': 6780880, 'author': 'Scott A', 'author_id': 786699, 'author_profile': 'https://Stackoverflow.com/users/786699', 'pm_score': 1, 'selected': False, 'text': "<p>Use Java when you absolutely cannot do it in PL/SQL, <strong>or</strong> if Java will allow you greater performance.</p>\n\n<p>As an example, if you wish to use sockets within a PL/SQL program (logging, external calls, etc), you can:</p>\n\n<ol>\n<li>write a PL/SQL client that uses UTL_TCP. There is no way to do UDP using only native PL/SQL though.</li>\n<li>write a Java client that uses TCP or UDP sockets.</li>\n</ol>\n\n<p>In the first case, you have a synchronous socket that can back up your PL/SQL calls if the remote service has issues. Additionally, if you are using dbms_session.reset_package (as in OWA), you will have to reconnect the socket for every request, which is very expensive.</p>\n\n<p>In the second case, TCP is still synchronous, but if you need asynchronous, non-blocking behavior you can use UDP. Additionally, reset_package does not reset Java TCP or UDP sockets, so you won't need to deal with tear-down/reconnect pain.</p>\n"}]
['https://Stackoverflow.com/questions/70072', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4491/']