Why use PL/SQL?

Here is the abstract of a talk that I've so far given at more than a dozen Oracle User Group conferences, starting in Israel in Jun 2015:

“Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface. This is the Thick Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance. But when developers follow the NoPlsql paradigm, their applications have problems in each of these areas and end-users suffer. This session provides PL/SQL devotees with unassailable arguments to defend their beliefs against attacks from skeptics; skeptics who attend will see the light.”

My presentation slides are no use without me presenting them. I have therefore written a whitepaper that gives a careful and detailed account of what my presentation addresses. HERE it is.

Note added May-2016

I gave this talk at the DOAG conference, in November 2015. (DOAG is the German Oracle User Group.) I summarized what I said in an interview. The six-minute video is published HERE.

Note added November-2016

I just published the post "Oren Nakdimon on #ThickDB". Do please read it and then download and watch Oren's talk. It substantiates, with real-world experience, everything I say in my "Why Use PL/SQL?" whitepaper.

Comments:

How do you suggest to sort out the biggest pain point in development (which is easier to manage with other languages) which is code versioning and merging?

Posted by chris on October 29, 2015 at 04:33 AM PDT #

Version control and configuration management (hereinafter VC/CM) is handled outside of the Oracle Database and must deal with the source files and other artifacts that define the application as a whole, across whatever tiers are relevant. Oracle Corp. doesn't have a VC/CM product; but various offerings are available from other vendors. SQL Developer integrates with several third party VC/CM systems.

Maybe you were asking about deploying the scripts that make changes to PL/SQL stored in the Oracle Database. This is straightforward, and my paper covers this. Use edition-based redefinition. This feature brings no licensing implications and is available in all currently supported versions of Oracle Database.

Posted by Bryn on November 07, 2015 at 10:30 AM PST #

Great piece, we work almost with the same method and it works efficient. From the database development side we do 1 step more, we also generate the piece of code for, in our situation, .Net, and put it in version control so that .Net developers can use the code after getting the new revision of the code. That saves a lot of time and so can both develop further.

Posted by Herald on December 26, 2015 at 03:27 AM PST #

I'm delighted to hear that, as far as you're concerned, I was just preaching to the choir. Generating the client-side stubs to improve the productivity of your .Net developers is a nice touch.

Posted by Bryn on December 27, 2015 at 05:54 PM PST #

Nice read. Thanks
How do you go about addressing automating schema deployments and rollbacks.

Posted by Fred Habash on April 01, 2016 at 06:52 AM PDT #

I responded to Fred Habash's comment with a brief new post in this blog called "Schema deployments and rollbacks in the regime of the thick database paradigm", here:

https://blogs.oracle.com/plsql-and-ebr/entry/schema_deployments_and_rollbacks_in

Posted by Bryn on April 01, 2016 at 05:03 PM PDT #

Hi Bryn, that you for the posts. We use PL/SQL for the database API layer and Java for the application layer. We are contemplating using JPA/Hibernate so that basic CRUD operations can be done in the JAVA layer without having to go through PL/SQL.

What are your thoughts on this? Especially from a performance point of view and from a maintainability point of view?

Thank you in advance,
Alan

Posted by guest on May 10, 2016 at 02:06 PM PDT #

Alan, I answer your questions preemptively in my "Why use PL/SQL?" paper. I can only assume, therefore, that you haven't read it yet. Please do so now. And then re-post your question explaining why you and your colleagues are thinking of making a change that, as my paper shows, will harm performance and maintainability. Your account must refute each argument that I make in favor of the Thick Database Paradigm with stronger arguments that favor the NoPlsql paradigm.

Posted by Bryn on May 17, 2016 at 07:40 PM PDT #

Hi,

I am also very interested in the real world arguments that development shops use to rationalise the removal of PLSQL and replace it with a middle tier ORM solution such as hibernate.

As this exact scenario was posted by Alan in May, I was wondering whether he could post more information about the initial proposal that prompted the original post, what his thoughts were after reading the white paper by Bryn and utilimately the decision that was taken.

Many thanks in advance
Mark

Posted by guest on June 12, 2016 at 10:00 PM PDT #

Bryn- folks have been trying to take work out of the database for a long time. Why? A variety of reasons, of course, but more often than not, in my experience, it's a mistake.

For other readers, PL/SQL has some of its roots in a language called ADA, widely used by the Department of Defense in the 80s and 90s.

Being able to spec out modules without providing the implementation is a powerful construct which allows large teams to move forward knowing only the entry points of modules built by other people within the team.

Naturally, this is something which other languages have borrowed from since then.

Posted by Tom Eastlake on September 23, 2016 at 11:36 AM PDT #

thanks

Posted by michell on October 31, 2016 at 01:27 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

These blog entries are typed up by Bryn Llewellyn, Distinguished Product Manager in the Oracle Database Division, Oracle HQ, responsible for PL/SQL and EBR. But the thinking usually comes from the engineers who built, and continue to enhance, these capabilities.

Twitter: @BrynLite

Search

Categories
Archives
« January 2017
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today