oracle-databaseplsqloracle11gsql-function

Reading Oracle boolean filed dbms_utility.is_cluster_database in statement


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:

  1. SELECT dbms_utility.is_cluster_database FROM DUAL; fails with message "ORA-06553: PLS-382: expression is of wrong type"
  2. SELECT CAST(dbms_utility.is_cluster_database AS INT) FROM DUAL; fails with same message
  3. SELECT sys.diutil.bool_to_int(dbms_utility.is_cluster_database) from DUAL; fails with same message
  4. SELECT 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;


Solution

  • 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';