I'd like to read boolean
field from Oracle Database (11g), but I'm unable to do so, probably due to Oracle not fully supporting boolean data format.
Field I'm interessted in is dbms_utility.is_cluster_database
.
Working statement:
set serveroutput on;
BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_output.put_line('true');
ELSE
dbms_output.put_line('false');
END IF;
END;
This statement is working fine, however I need it as SQL query, like SELECT 'someValue' from dual;
I've tried:
SELECT dbms_utility.is_cluster_database FROM DUAL;
fails with message "ORA-06553: PLS-382: expression is of wrong type"SELECT CAST(dbms_utility.is_cluster_database AS INT) FROM DUAL;
fails with same messageSELECT sys.diutil.bool_to_int(dbms_utility.is_cluster_database) from DUAL;
fails with same messageSELECT CASE WHEN (dbms_utility.is_cluster_database) THEN 1 ELSE 0 END AS MY_BOOLEAN_COLUMN FROM DUAL;
fails with message "SQL Error: ORA-00920: invalid relational operator"I'm out of ideas how it can be fixed.
Problem is definitely not related to database access rights (since "dbms_output" solution works). Also, other fields from dbms_utility
can be read using simple SELECT dbms_utility.<something> from dual;
, eg. SELECT dbms_utility.get_endianness FROM DUAL;
i guess it's not possible to read boolean values in SQL, but you can write a wrapper function which would analyze that boolean and return varchar2/int/number.
alternatively, in your particular case you can do:
select value from v$parameter where name='cluster_database';