Passing a PL/SQL Boolean Parameter in a SQL clause

Oracle SQL does not have a boolean data type. Here is an example how to pass a PL/SQL boolean parameter to a function in a SQL statement. This is using 12c version ability to declare a function in with part of a query.

rollback;

with function begin_transaction return varchar2 is 
 begin
  dbms_lock.sleep(2);
  return dbms_transaction.LOCAL_TRANSACTION_ID(TRUE);
 end;
select systimestamp beforetime
     , dbms_transaction.LOCAL_TRANSACTION_ID not_in_a_transaction
     , begin_transaction
     , dbms_transaction.LOCAL_TRANSACTION_ID inside_a_transaction
     , systimestamp aftertime
  from dual
;

beforetime                not_in_a_transaction  begin_transaction  inside_a_transaction  aftertime
27.06.2017 16:44:23,134   (null)                5.24.3524          5.24.3524             27.06.2017 16:44:23,134

SQL function systimestamp is returning consistent results inside a cursor. Columns beforetime and aftertime returns the same time allthou pl/sql function call to declared begin_transaction is coded to take two seconds in between. PL/SQL function calls inside a SQL clause have some order in which they are executed as one can see form this example results. The first call of dbms_transaction.LOCAL_TRANSACTION_ID for not_in_a_transaction returns null. Second call for dbms_transaction.LOCAL_TRANSACTION_ID function starts an transaction as it gets TRUE parameter. The third call returns the transaction id in inside_a_transaction.