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, 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:

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


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 )

Facebook photo

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

Connecting to %s