Lutz Haake | Freelance Oracle Consultant

Try this:

select true from dual;
ORA-00904: "TRUE": ungültiger Bezeichner

The reason behind this behavior is that TRUE and FALSE are only available in PL/SQL, not in SQL. Booleans are simply not usable in SQL queries.

How to work around this limitation? The SYS.DIUTIL package’s BOOL_TO_INT() function provides a hint. Let’s see.

declare
  n number;
  v_true boolean := true;
  v_false boolean := false;
begin 
  dbms_output.enable(null);
  select SYS.DIUTIL.BOOL_TO_INT(v_true) into n from dual;
  dbms_output.put_line('true: ' || n);
  select SYS.DIUTIL.BOOL_TO_INT(v_false) into n from dual;
  dbms_output.put_line('false: ' || n);
end;
/

true: 1
false: 0

At this point, one can assume that ORACLE internally represents TRUE and FALSE by the numbers 1 and 0.

Lets try some more. Here’s a test setup which uses SQLTEXT_TO_SIGNATURE with the same SQL structure and varia­tions of the query parameter and the force_match parameter.

select * from 
(
select 'A with default force match' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''') signature from dual
union all
select 'B with default force match' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''B''') signature from dual
union all
select 'B with force match = 0' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''B''', force_match => 0) signature from dual
union all
select 'B with force match = 1' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''B''', force_match => 1) signature from dual
union all
select 'A with force match = 0' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''', force_match => 0) signature from dual
union all
select 'A with force match = 1' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''', force_match => 1) signature from dual
union all
select 'A with force match = 2' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''', force_match => 2) signature from dual
union all
select 'A with force match = 3' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''', force_match => 3) signature from dual
union all
select 'A with force match = -1' case, DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT USERNAME FROM DBA_USERS WHERE USERNAME = ''A''', force_match => -1) signature from dual
)
order by 2,1
;

My conclu­sions:

• using the default force_match and force_match=0 yield identical results. This confirms the assumption that FALSE and 0 can be used synonymously.
• using force_match=1 yields a different result that force_match=0 . Yet it yields the same result for A and B, which is the expected behavior for force matching. This confirms the assumption that TRUE and 1 can be used synonymously.
• Appar­ently, more than one value can be used to represent TRUE: the signature is the same for force_match = ‑1, 1, 2, 3 and probably other values also. It may be assumed that any nonzero value can be used to represent TRUE.