PL/SQL Server Pages - How does it compares to other technologies (e.g. PHP)? How popular is it?

Question

Today my university lecturer in a databases class brought up PSP.

I have used the LAMP stack and am into open source, so I have a bias against Oracle. I understand however, that Oracle databases are used in industry and so it is good to learn how to use it.

However when we covered PL/SQL Server pages (Stored procedures in an Oracle database that output HTML and can be access with a web browser) I cringed. Is my bias getting in the way too much, and there are advantages to using it compared to using something like PHP and dynamic SQL queries? How popular is PSP in web applications using Oracle?

Answer 1

PL/SQL Server Pages (PSPs) are (sort of) a variant of the more general-purpose PL/SQL Web Toolkit (also known as OWA; it was called the Oracle Web Agent in early days).

There is not much buzz around PSPs, so I assume they are used by very few. The PL/SQL Web Toolkit (OWA), however, is another matter altogether. Lots and lots of people use this for building (data-centric) web applications with the power of PL/SQL.

Also, in 2006 or thereabouts, Oracle released its free (no-cost option) for the database called Oracle Application Express (Apex), which is built on top of OWA, and is now (at the time of this writing) in version 4.1.

Apex gives you a browser-based IDE for developing PL/SQL web applications (powered by OWA). Apex also includes a whole bunch of built-in features such as session management, authentication and authorization, interactive reports, flash charts, page templates, navigation, Ajax support, and much, much more.

So if you are interested in looking into web development with Oracle, I suggest you forget about PSPs and look into Apex instead.

In terms of popularity, according to this Powerpoint presentation from Oracle from 2010, Apex had at that time:

• 80,000 downloads per year
• 4,000,000 page views per week on apex.oracle.com
• Third largest discussion forum on forums.oracle.com
• 100 consulting companies delivering Apex services
• 60 blogs about Apex
• Used as the user interface for the Oracle Store and Oracle Audit Vault

Answer 2

PSPs are amazing. They have been around for a very long time and have not have gotten much attention. In the realm of web development most people simply do not know they exists and therefore are not considered in the architectural system design.

Yes, Oracle is closed source and expensive. So it’s not the cheapest path. Then again, if an Oracle database is needed and you’re buying it anyway, then the cost is $0 as the right to use PSPs is bundled with the Oracle database licenses.

PSPs have an advantage over PHP and other middleware products because the database generates the html text. For a dynamic website, this equates to performance.

Say for example an html page needs to execute 10 queries in order to piece the page together. With middleware, the data from the 10 queries must travel from the database to the middleware, then the middleware parse and assembles the data and finally delivers the context to the web.

With PSPs, the web server communicates directly to the database. So the database executes its 10 queries internally, assembles the page and delivers the text to web server in one pass.

There are times when a middle layer server is helpful, in my opinion, you need to be doing more than serving web pages to justify the middle layer need.

Furthermore, PSPs provide a way to create your own syntax using tools such as tcl. You can write a tcl script to translate your syntax to what Oracle understands and then have Oracle compile and load it into the database.

As computers get more powerful, more processing can move to the database tier.

Anyway, hard to explain all of the benefits of PSP. Simply put, hardly anyone knows they exists, hardly anyone uses them, they are extremely powerful and are easy to use. I have yet to find anything better.

---

Followup 8/2015

One thing I wanted to mention--Oracle Apex Developers are using the Oracle Web Toolkit-- which is the same technology as Oracle's PSP technology.

PSPs create Oracle Stored Procedures which output html code. Apex and PSPs both do this the same way.

To this day I've yet to find a better way to serve dynamic web pages. There is a reason why Oracle calls their web product Apex, as this architecture is like no other and has many many advantages over traditional middle tier architectures (such as PSPs, JSPs, Coldfusion, Python, etc...). The bottom line is that you can skip the whole middle tier and serve dynamic-database driven--web pages.