Tuesday, June 3, 2008

How PL/SQL Could Be More Like PHP

This is really a trick subject line -- PL/SQL already has the capability to be very much like PHP. PL/SQL Server Pages lets you write template HTML files including PL/SQL parameters, expressions, and blocks, and turn them into stored procedures that produce HTML output using HTP.P() procedure calls. The angle-bracket syntax around the expressions and blocks is very similar to that of PHP. And a PL/SQL coder has a headstart when it comes to optimizing DB performance and avoiding security problems.

PL/SQL Server Pages was the first web-related feature that I documented at Oracle. When I started using it myself, eating my own dogfood so to speak, that's when the potential functionality enhancements sprang to mind.

PHP is so popular because it's all based around text files, you can modify your source with any kind of HTML tool, and you don't have to do anything special to get the code to run, just put the files in the right places.

PL/SQL Server pages are also based around text files, you can also run them through HTML Tidy, and edit them in DreamWeaver or what have you. However, to use them requires the loadpsp command. With this command, you specify the user ID and password on the command line. That's not the ideal mode of loading code if you normally sit at the SQL*Plus command prompt, already logged in, and load new/changed stored procedures via '@script_name'.

With PHP, the way to load code into different parts of the application structure is to put the files in different subdirectories. The equivalent for PL/SQL Server Pages would be to use a template file to generate a packaged subprogram, an internal procedure, or a method for an object type. But, to my knowledge, loadpsp can only create a top-level procedure, which best practice says you should keep to a minimum.

Now, all of this could be worked around if loadpsp could generate the source code for the procedure and stop there, leaving the file behind instead of loading it into the database. You could set up a makefile to transform each .psp file into a .sql file. You could load the .sql files into the database from inside SQL*Plus without doing a separate login each time. You could set up a script using a SQL*Plus HOST command to go through both steps. Perhaps that would incite the SQL*Plus dev team to create a new directive similar to '@' that would run loadpsp and then '@' the resulting output file. If the output file could start with 'PROCEDURE ... IS', leaving off the initial 'CREATE OR REPLACE', you could use my favorite trick of:

package body foo is

to load PSP-generated pages as packaged procedures or object methods.

I never quite bought into the idea that with PHP, coders and designers would happily edit the same files, passing them back and forth and limiting their edits to either the HTML tags or the code blocks depending on their role. In my experience, every expert tends to have ingrained assumptions that don't work well with top-down planning. The designer tasked with making a more attractive-looking page will go off and write HTML that's structured very differently from the existing page, that PL/SQL blocks and widgets can't be neatly dropped into. The PL/SQL coder tasked with adding some dynamic elements to the page will chafe at working inside markup produced by some HTML authoring tool they disdain; sometimes, it's too painful even to read past the DOCTYPE tag. :-)


Chris said...

PSP's habit of only generating top-level procedures stopped me from using them. I never used that either, but there is a product of a russian company that overcomes this limitation, see http://www.dynamicpsp.com/!go?id_=p_dpsp2.

I wouldn't be satisfied with

package body foo is

either. It still requires one to write a package specification. Btw, this is one thing that I would change first in PL/SQL if I could (see how Oberon handles visibility, for example).

However, it should be possible to generate a small domain specific language with SQL*Plus scripts, something like this:

@package_begin foo

@proc procedure1
< table >
< %for i in ...% >

@proc procedure2
( i_arg1 in number
, i_arg2 in varchar2
x varchar2(30);


The scripts will have to forward the package name, procedure names, and strings between them to a code generation package. Package_end has to start spooling to a file, trigger the generation (which uses dbms_output) and execute the spooled file. Unusual, but it shouldn't be difficult.

There are drawbacks, of course, like losing error line number info. Besides that, this has nothing to do with PHP, anymore ;-).

Regarding your coders/designers paragraph, this requires a very clean MVC separation. The view should never cause side effects and even cursor for loops might be too difficult for the designers. Just yesterday I read an interesting article about a functional template language for Java with strict model/view separation, http://www.stringtemplate.org/. Although I really like PL/SQL, I think we are too limited in the datatype department for doing something like this.

JCFlack said...

Oracle is not the only source of software to support PSPs. DPSP-Yes produces something called Dynamic PSP and Objective PSP, with some of the features you'd like. This runs in Joppa which is a Java servlet that does something much like the mod_plsql module for Oracle's version of Apache or the EPG feature of Oracle 10g and 11g. A company from Denmark called ChangeGroup also used to have an alternative to loadpsp, but they don't seem to sell it anymore. I haven't tried either of these alternatives, so don't say I told you to buy it.

John Russell said...

foo bar

Chris said...

I prefer fnord.

Anyway, I created an open source project out of my initial idea above. Here is the announcement.