X

PL/SQL and edition-based redefinition:
the perfect marriage.

  • October 27, 2015

Why use PL/SQL?

Bryn Llewellyn
Distinguished Product Manager

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.

Join the discussion

Comments ( 11 )
  • chris Thursday, October 29, 2015

    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?


  • Bryn Saturday, November 7, 2015

    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.


  • Herald Saturday, December 26, 2015

    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.


  • Bryn Monday, December 28, 2015

    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.


  • Fred Habash Friday, April 1, 2016

    Nice read. Thanks

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


  • Bryn Saturday, April 2, 2016

    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


  • guest Tuesday, May 10, 2016

    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


  • Bryn Wednesday, May 18, 2016

    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.


  • guest Monday, June 13, 2016

    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


  • Tom Eastlake Friday, September 23, 2016

    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.


  • michell Monday, October 31, 2016

    thanks


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services