I Think About George Boole Quite Often

George Boole is dead. Long live Booleans. https://en.wikipedia.org/wiki/Boolean

Especially when it comes to boolean data types being forever restricted from seeing the light of day outside of PL/SQL. How many times, before Oracle Database 12.1.0.2, have you wanted to do this with a library function that returns a BOOLEAN PL/SQL data type:

select my_utils.does_col_exist('HR','JOBS','JOB_ID') from dual;

Error report -
SQL Error: ORA-00902: invalid datatype

In Oracle SQL, you have to somehow convert the return value of DOES_COL_EXIST to something supported by Oracle SQL. In this case, just put a PL/SQL function in the WITH clause:

with 
   function george_boole_escapes_house_arrest
      (ownname varchar2, tabname varchar2, colname varchar2)
   return number
   is
   begin
      return
      case 
         when my_utils.does_col_exist(ownname,tabname,colname)
            then 1 
            else 0 
      end;
   end;
select george_boole_escapes_house_arrest('HR','JOBS','JOB_ID') 
from dual;

GEORGE_BOOLE_ESCAPES_HOUSE_ARREST('HR','JOBS','JOB_ID')
-------------------------------------------------------
                                                      1

Problem solved. You can have multiple PL/SQL functions in the WITH clause; you can even have PL/SQL procedures in the WITH clause. Defining the function in-line also reduces context switching between SQL and PL/SQL. So many cool features, so little time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s