File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Regarding pl/sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Regarding pl/sql" Watch "Regarding pl/sql" New topic

Regarding pl/sql

sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Can anybody tell me the advantages of PL/Sql with real life example and where exactly we can use it?. I am learning it by myself so please help me to get know more about it.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33134

Pl/SQL is a language for writing stored procedures. You would use it when you want to do processing in the database itself. For example when you would need to return too much data to calculate something.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Thank you but you did not tell clearly, I asked an example about that...
bhanu chowdary
Ranch Hand

Joined: Mar 09, 2010
Posts: 256
sudharani Savadi wrote:Thank you but you did not tell clearly, I asked an example about that...

Jeanne has given an example
For example when you would need to return too much data to calculate something

what are you expecting?? A pl/sql block?
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

No, I am not expecting any block of pl/sql code and I know how to write that. I could not understand its usage i mean when and where exactly we can use this?
chris webster

Joined: Mar 01, 2009
Posts: 2296

Jeanne's reply is correct - it is for doing procedural logic in the database - but this simple description does not really tell you much.

PL/SQL has been around for about 20 years as a standard part of the Oracle RDBMS (it is also available on PostgreSQL these days). Before that, Oracle developers often had to use procedural languages outside the DB to implement complex data-oriented functionality, usually combining a 3GL like C or COBOL with chunks of SQL (a bit like basic Java/JDBC and SQL). This meant a lot of context-switching and traffic between the external process and the database, extra pre-compilation steps, complicated coding, and a general tendency to fall over in a smoking heap as soon as you changed anything. Great for people who like hunting for pointer errors, but not so much fun if you're trying to get an Oracle application built.

PL/SQL allowed Oracle developers to implement all of this complex logic in one place instead, using a uniform language that increasingly provided a lot of useful features - clean integration of SQL and procedural logic, functions/procedures/packages, table triggers, robust exception handling, anchored data types (a sort of semi-dynamic typing where you tell your variable it should use the type from a particular DB column as its data-type, so you don't have to change all the variable declarations if the column length changes for example), performance improvements based on optimisations within the DB engine, an ever-expanding set of package libraries, and so on. This allowed developers to encapsulate much of the complexity of their systems in the database behind relatively clean interfaces, compared to the complicated spaghetti of Pro*C in the bad old days. Many of these facilities exist on other DBs as well, and most enterprise DBs now have some kind of procedural SQL extension similar to PL/SQL, because it's really useful.

Today PL/SQL is massively powerful, heavily optimised and immensely flexible, and you can implement pretty much anything you want using PL/SQL within the database - business logic, data-tier logic, even presentation logic to a large extent. And using Oracle's PL/SQL based Application Express tool (free on all Oracle RDBMS platforms) you can even implement fully functional data-oriented web applications very easily.

Of course, different people have different ideas about when/where they want to use PL/SQL. If your system treats the RDBMS as basically a big dumb passive data-store, and you want to keep all your logic in the Java tiers and just read/write data via an ORM, then you probably won't have much use for PL/SQL. Or if you want to be able to switch DB platforms easily, the obviously you won't want to invest too much effort in Oracle-specific tools like PL/SQL.

But if you are committed to the Oracle DB platform and want to make the best use of the tools it provides, if you have a lot of data-intensive processing to do, if you want to avoid moving lots of data back and forth to the JEE server so your Java business logic can process it, or if you want to encapsulate your complex DB operations behind a simple set of services/interfaces, then you might seriously want to think about using PL/SQL to implement much of your data-oriented logic.

So good for you for taking the trouble to learn something about this powerful additional toolset for Oracle database application development!

No more Blub for me, thank you, Vicar.
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Thank you very much for clearing my doubts very nice.. whenever I get doubts, i will post so do reply for that. Have a wonderful day.
I agree. Here's the link:
subject: Regarding pl/sql
It's not a secret anymore!