ORA-00920
There're several error patterns of ORA-00920.
Parenthesis Problem
An extra right parenthesis ")" can cause ORA-00920.
SQL> select 1 from dual where to_char(sysdate + 1)) > sysdate;
select 1 from dual where to_char(sysdate + 1)) > sysdate
*
ERROR at line 1:
ORA-00920: invalid relational operator
Did you see that? An extra right parenthesis cannot be paired.
BOOLEAN Problem
Tried to convert BOOLEAN into string by CASE WHEN conditional clause, but it failed with ORA-00920.
SQL> show user
USER is "SYS"
SQL> select case when dbms_utility.is_cluster_database then 'YES' else 'NO' end from dual;
select case when dbms_utility.is_cluster_database then 'YES' else 'NO' end from dual
*
ERROR at line 1:
ORA-00920: invalid relational operator
In the statement, DBMS_UTILITY.IS_CLUSTER_DATABASE is a function which returns BOOLEAN to let us judge whether the environment is a cluster one or not.
BOOLEAN Data Type
ORA-00920 means that the operator or data type in the SQL statement is not defined or cannot be recognized by SQL engine, in fact, the BOOLEAN data type that the function returns belongs to PL/SQL.
Doesn't Oracle have BOOLEAN data type? The answer is yes and no. The fact is that, PL/SQL does have BOOLEAN data type, but SQL as well as column don't have BOOLEAN data type before release 23ai.
Release 23
From release 23ai, Oracle starts to support BOOLEAN data type for SQL officially.
Logical Operations
Logical operations like the following SQL statement is different from BOOLEAN data type in PL/SQL.
SQL> select case when 1=1 then 'YES' else 'NO' end from dual;
CAS
---
YES
Solutions
For extra right parenthesis, removing unpaired parenthesis can solve the problem.
SQL> select 1 from dual where to_char(sysdate + 1) > sysdate;
1
----------
1
PL/SQL Block
For BOOLEAN caused errors, we can only manipulate the function that return BOOLEAN in PL/SQL, not in SQL. So the solution is to use the function in a PL/SQL block.
Let's see an example.
declare
v_result varchar2(10);
begin
v_result := case when dbms_utility.is_cluster_database then 'YES' else 'NO' end;
dbms_output.put_line('Is is a cluster? ' || v_result);
end;
/
In the PL/SQL block, we still use CASE WHEN conditional clause to convert BOOLEAN into string.
Let's see the execution result.
SQL> set serveroutput on;
SQL> declare
2 v_result varchar2(10);
3 begin
4 v_result := case when dbms_utility.is_cluster_database then 'YES' else 'NO' end;
5 dbms_output.put_line('Is is a cluster? ' || v_result);
6 end;
7 /
Is is a cluster? YES
PL/SQL procedure successfully completed.
OK, it's a cluster database.
TO_CHAR (boolean)
For release 23 and later, function TO_CHAR is able to convert a BOOLEAN value into TRUE or FALSE string.
SQL> select to_char(dbms_utility.is_cluster_database) result from dual;