Designing a PL/SQL API – BOOLEAN or CHAR?

A simple question: you’re designing an API to be implemented as a PL/SQL package, and you don’t (yet) know the full extent to which your API may be used, so you want to cover a reasonable variety of possible usage cases.

You have a function that will return a BOOLEAN – i.e. TRUE or FALSE (or perhaps NULL). Should you implement it this way, or should you return some other kind of value – e.g. a CHAR – e.g. ‘Y’ for TRUE or ‘N’ for FALSE; or how about a NUMBER – e.g. 1 for TRUE or 0 for FALSE?

This debate has raged since 2002, and probably earlier – e.g. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595

Well, if I use a BOOLEAN, it makes the code simple and easy to understand – and callers can call my function in IF and WHILE statements without having to compare the return value to anything. However, I can’t call the function from a SQL statement, which can be annoyingly restrictive.

If I use a CHAR or NUMBER, I can now call the function from SQL, and store it in a table – but it makes the code just a little more complicated – now, the caller has to trust that I will ONLY return the values agreed on. Also, there is no way to formally restrict the values as agreed – I’d have to just document them in the package comments. I can help by adding some suitable constants in the package spec, but note that Oracle Forms cannot refer to these constants directly. Mind you, if the value is being stored in a table, a suitable CHECK constraint would be a good idea.

Perhaps a combination? Have a function that returns BOOLEAN, and add wrapper functions that converts a BOOLEAN into a ‘Y’ or ‘N’ as appropriate? That might be suitable.

Personally, though, I hate the NUMBER (1 or 0) idea for PL/SQL. That’s so C-from-the-1970’s. Who codes like that anymore?

3 Reasons to Hate Hibernate
DEFAULT NULL NULL

Comments

  1. That’s really one of the unbelievable “features” of Oracle…

    Another drawback of the BOOLEAN type in Oracle: BOOLEAN function return values or OUT parameters cannot be transported via JDBC. That’s even pre-C-from-the-1970s… 🙂

  2. I tend to have both, with a PL/SQL function returning a boolean and a SQL-friendly one returning either YN or TF (with the function suffixed _YN or _TF).
    I once met a function that returned Y for Yes, F for False and N for Null/Unknown. Apparently it started up as a True/False/Null but someone wanted the ‘T’ replaced by Y and it was too late/too hard to replace the N that was being used for Null. Scary.

    This leads to the next question of which becomes the wrapper, and which is the wrapped. A ‘Boolean to VARCHAR’ wrapper can be re-used but on the other hand you can have one function returning a character being used by multiple boolean returning functions. For example a function returning ‘ACTIVE’,’CLOSED’,’PENDING’ can be used for a set of boolean-returning functions of is_active, is_closed and is_pending.

    I think there’s no single best answer which is why it gets debated a lot.

  3. I go for YN (in a VARCHAR2) with some generic functions – toBoolean(), isTrue(), isFalse() – to convert them to BOOLEAN, if requried.

    One reason is the lack of BOOLEAN support in SQL but another is the lack of implicit character conversion in PL/SQL; I don’t want to do “CASE WHEN THEN ‘T’ ELSE ‘F’ END” just to get the value.

  4. I also like to overload, with the boolean function calling the varchar2 version – one less layer when including in SQL

Leave a Reply

Your email address will not be published / Required fields are marked *