1. November 2021
Design flaws in Oracle PL/SQL and ideas how to fix them
Thinking about Oracle PL/SQL and SQL flaws and how they could be fixed
Background
First of all, let me say that I like Oracle PL/SQL.
I've been working with it since 1994, and it is remarkable that a programming language lasts such a long time.
Jist think of other languages that have come and gone in these decades.
In our company, there have been some constants more or less since I started in that year, and these were:
- The Oracle database
- SQL
- PL/SQL
- Java
- Python
- C#
A main reason why our company does not pick up new trends frequently is the kind of software that we are developing (a LIMS) and the kind of our customers (pharmaceutics, industry). The software has an impact to the way our customers are working comparable to e.g. SAP. Thus it intended to run for at least a decade when installed. Of course this doesn't mean there are no updates, but it means that we need a very reliable framework.
So most of the business logic was and still is implemented in PL/SQL, while the technologies for UI and reporting changed over time.
Reasons why I like PL/SQL
It is remarkable that AFAIK there have never been severe (e.g. security) problems with PL/SQL in all this time. This may be related to the fact that PL/SQL is somewhat "old-fashioned".
It's tight integration with SQL makes developing business logic for (Oracle) database applications very easy.
It supports NULL so in this regard it matches SQL exactly.
It is easy to learn and easy to read.
It is very stable and solid - I mean the running programs as well as its development over the decades.
What is not so good (but cannot be changed)
PL/SQL is quite verbose.
String processing is often cumbersome.
It counts 1-based and not 0-based (this matches SQL and saying this not so good is just my personal opinion).
The design flaws
From my experience, the following are serious design flaws which often cause hard-to-find errors.
Implicit type conversion
There are implicit type conversions everywhere. I think this is the number one design flaw in most programming languages, but in SQL and PL/SQL it is worse because in these languages the conversions are NLS dependent by default - and the languages have been designed by Americans who probably were not aware of this topic.
In the following examples, the variable names reflect their type:
v_date := v_str; -- IMPLICIT: TO_DATE without a format string
v_str := v_date; -- IMPLICIT: TO_CHAR w/o format
v_num := v_str; -- IMPLICIT: TO_NUMBER w/o format
v_str := v_num; -- IMPLICIT: TO_CHAR w/o format
v_str + v_num; -- IMPLICIT: TO_NUMBER w/o format
v_dat + v_str; -- IMPLICIT: TO_NUMBER w/o format
v_str || v_num; -- IMPLICIT: TO_CHAR w/o format
v_str || v_dat; -- IMPLICIT: TO_CHAR w/o format
The implicit conversion between various number data types can cause trouble as well.
To reduce errors, it should not be possible to mix them in assignment or calculations, because they can cause a precision-loss or rounding errors or conversion errors (when converting between internal 10-based storage and 2-based storage.
For conversion between strings and numbers: The results (or occurence of exceptions) depend on the NLS_NUMERIC_CHARACTERS. Furthermore, in Germany we usually write a leading zero for values between 0 and 1, e.g. '0,25' instead of '0.25' for a quarter.
On TO_NUMBER, the wrong decimal and group character can cause immediate exceptions or (much worse!) a result can be mis-interpreted, e.g. 1.234 means 1234 in German (with point as a thousands separator) but 1234/1000 in US English.
On TO_CHAR, there will be an exception, but mis-interpretation (by a human reader or another program when it tries to interpete the string as a number later) can still occur.
On TO_DATE, the result depends on the NLS_DATE_FORMAT setting. E.g. in some countries, the default may be MM-DD-YY while in Germany DD.MM.YYYY is common. So this can result in an exception or in a misinterpretation just like with numbers.
Each and every implicit conversion should be reported as a PL/SQL warning.
Each and every TO_NUMBER and TO_DATE without a format should be reported as a PL/SQL warning.
BTW if there was no implicit conversion, then the + operator could be used for strings in a safe manner, we would not need the || operator.
All string types can be assigned despite the maximum length
This is a special case of implicit type conversion.
Say, you have a source string v_source which is from a VARCHAR2(1000) column and a destination variable v_dest declared as VARCHAR2(3). You can assign
v_dest := v_source and if the actual length is >3 you'll get a VALUE_ERROR exception.
Strings with different maximum lengths shouldn't be assignable without explicit conversion if the source can be longer than the destination or if at least one of it has unknown length.
And for the explicit conversion, one should be able to tell the compiler if a too-long input value should result in a VALUE_ERROR or be truncated silently. Maybe two different functions should be introduced or an optional parameter silent_truncate.
Maximum length and nullability of parameters and return type cannot be declared or accessed
This is related to the previous problem.
You cannot declare a parameter as VARCHAR2(30), only as VARCHAR2 with unknown length. And even if your parameter is declared as EMP.ENAME%TYPE (where the maximum length is known from the column ENAME of the EMP table), you cannot access the length inside the function.
This is a problem in particular for output parameters and return values. E.g. you generate some error message or comment for an output parameter or the return value and you want to give as much information as possible, but you have to avoid a VALUE_ERROR.
For return values, another effect of this problem is that a column which is the result of a VARCHAR2 function always has a length of 4000.
Wouldn't it be possible to have attributes for columns, variables and parameters like the following?
xxx%MAX_CHARS returns the maximum number of characters of xxx or NULL if the maximum length is unknown.
xxx%NULLABLE returns 1 if the compiler knows that xxx may contain nulls, 0 if the compiler knows that xxx must be NOT NULL and NULL if the compiler does not know.
Note that I do not mean the compiler that e.g. an input variable declared as a PK column %TYPE must be NOT NULL. This would change existing behavior too dramatically.
Actually checking that all input parameter values declared as NOT NULL really are NOT NULL on input and that all NOT NULL output columns and the return value are NOT NULL on output (exception when an exception occured) could be done with a PRAGMA CHECK_NOT_NULL.
Calling procedures/functions with a lot of parameters without =>
A quite common cause for subtle bugs is to define procedures or functions with a lot of parameters.
This is often the easiest way to achieve a goal, but if you have a function with more than a few parameters, it should be an error to call it with positional assignment without explicitly assigning every (used) parameters with the pi_name => v_value syntax.
It is hard to define a fixed value for the maximum allowed number of parameters for positional assignment, but I think a maximum of 5 is ok.
Dear Oracle: This could be easily assed as a PL/SQL warning.
For more than 5 arguments, the risk of a wrong ordering of the arguments is too high.
Lack of a type registry
What I mean here is best shown with a simple example. Just think of the SCOTT/TIGER example schema with its DEPT and EMP tables.
The DEPTNO is declared as NUMBER(4) for example. In the EMP table, we also have a DEPTNO column referencing the DEPT table. We have to duplicate the NUMBER(4) here. If we later have a really big company with longer DEPNTO values - e.g. NUMBER(6), then we have to remember to not only change the column in the DEPT table, but also in the EMP table. Okay, in this special case one could argue that it is easy because the column name is the same, but things are not always so easy.
If we had one or more schema-specific type registry (for example given in a package with a special name or pragma and without a body), we could have a type DEPTNO_TYPE and use that in the CREATE TABLE statements for the DEPT and the EMP tables instead of duplication NUMBER(4).
This does not work unfortunately, because definitions of table column types cannot use packages. But I see no reason why this rule couldn't be relaxed.
Like this:
create or replace package MY_TYPES
is
PRAGMA PROVIDES_COLUMN_TYPES;
DEPTNO_TYPE NUMBER(4);
EMPNO_TYPE NUMBER(5);
...
end MY_TYPES;
/
create table DEPT
( DEPTNO MY_TYPES.DEPTNO_TYPE NOT NULL
, ...
);
create table EMP
( EMPNO MY_TYPES.EMPNO_TYPE NOT NULL
, ENAME varchar2(30)
, DEPTNO MY_TYPES.DEPTNO_TYPE
, ...
);
Note that actually implementing OR REPLACE will be difficult, in particular if one decides to shorten a type. This could result in ALTER TABLE MODIFY etc. being necessary.
Exception handling
The exception handling mechanisms were probably excellent back in the old days when the language was introduced, but today - well, I see some things that could be a lot better.
First of all, the exceptions are unstructured. They have unique numbers, which is good, but there is no kind of hierarchy. For example, you cannot easily decide between problems with the data (such as TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX) or problems with the database itself such as corrupt blocks or whatever.
Aonther problem with exceptions is that there is no equivalent to the finally clause of other programming languages. You need this to free resources in many cases. The only thing you can do is something like
EXCEPTION
WHEN OTHERS THEN
CLEAN_UP();
RAISE;
END;
This works, but the RAISE loses information about the location where the exception originally occured.
And a third problem - at least for bigger systems like ours (with hundreds of tables and packages) is the limited way of defining your own exceptions. With RAISE_APPLICATION_ERROR you only have a global maximum of 1000 different error codes. This means that the error code alone is not sufficient for example to translate error messages. It would help a lot if one could add different custom error prefixes instead of the auto-generated ORA prefix. That way the unique key for an error would be the tuple(PREFIX, SQLCODE) instead of just SQLCODE.
A forth problem is that you cannot add additional properties to an exception: Just the SQLCODE and the text in SQLERRM. It would be cool if you could for example add some information from the application domain in some kind of key->value dictionary.
Getting exception handling right is difficult in PL/SQL - just like in many other programming languages. The best exception handling is nothing at all, except handling exactly only the expected exceptions like TOO_MANY_ROWS, NO_DATA_FOUND or DUP_VAL_ON_INDEX. And to use WHEN OTHERS only if really needed to clean up resources, followed by a RAISE (but as mentioned before, this looses information about the exception's origin).
Other issues
CAST does not allow %TYPE
Sometimes you want to use %TYPE more or less just as a SUBSTR to truncate a string to the maximum allowed length for a given column. Can't remember an example now, but I had this problem several times.
A similar problem was when declaring types (at schema level, not inside a package).
The prefix FX for date conversion is not the default
For those who do not know (which means: most PL/SQL developers in my experience), the TO_DATE conversion can give unexpected results if the input string is shorter than expected. The FX prefix means that the input string must match the format pattern exactly.
Labels: Oracle, PL/SQL, Programming, SQL
