Implementing an Application in PL/SQL

Question

We're developing a web application which according to my spec must have the backend written entirely in PL/SQL (stored procs etc.). Anyone have any advice/links on how to write a well structured backend using stored procedures and custom types? Normally I would have a business layer where all this would happen but what the employer wants the employer gets and so on.

Answer 1

I'd read the spec to say that you are not to construct or embed SQL statements in your procedural code, which I hope you would concur is a good plan in any case.

PL/SQL is not a language in the same sense as Python or PHP. So whatever you would normally do in your "business layer" you'll still do the same way. The difference would occur in the DAL (you do have one, don't you?). Rather than SQL, you would be consuming PL/SQL as function calls with arguments. It becomes your ORM, if you will, but constructed within the database (and more functionally oriented than object-oriented, perhaps).

Answer 2

I don't know what your area of reponsibilities exactly is but you have an obligation of advice to your client. I think we all know what he is asking for is a distater-to-be if the scale of the application is big, prone to grow or if the project is ill-specified (i.e agile environment).

Anyway, the client does not expect you to generate HTML straight out of the database, does he?

That being said, I worked on a gigantic application that deferred a lot (too much I'd say) to the database (not PL/SQL but Oracle AND MSSQL -- which meant two sets of stored procedures -- and sometimes specific tables, views, triggers & constraints -- to maintain). What I'd say from a general point of view:

• Read, read, read
• Work you schema to the detail first (don't hesitate to break things into a lot of different tables and to use views to join them and get a simpler interface to your database),
• If PL/SQL custom types are good then hide your top-level tables/views behind custom types (always),
• Think modular and "service" to organize your procs,
• Don't try and be smart/fast right from the beginning.

What I mean is a structure like this:

• BUSINESS: stored procedures & custom types
• APPLICATIVE: views, tables, internal procs, internal types
• STORAGE: base schema (base tables), triggers & constraints (manipulate with care)

Oh yes and refactor early. The bigger the application, the harder it is to move things around especially into such an environment.

Of course I don't need to tell you to test early ;)

Answer 3

You can still have a business layer - it's just that you are writing it in PL/SQL instead of some other language.

A typical PL/SQL business layer would use packages for each major area of functionality, with appropriate procedures and functions in it e.g.

create package employees_pkg as
    procedure hire_employee (p_id integer, p_name varchar2,
                             p_start_date date, ...);
    procedure terminate_employee (p_id integer, p_end_date date);
    ...
end;

These packages can perform DML directly against the tables, though some (not I) would advocate a "table API" layer below this so that employees_pkg.terminate_employee would call "employee_tapi.update(...)" instead of "UPDATE employees...", which seems pointless to me.

Custom types have their uses, but I wouldn't go overboard and try to build an OO layer in PL/SQL.

You don't say what your client application is built with, but Oracle Application Express would be a great choice for a web-based Oracle database application.