Reading Oracle boolean filed dbms_utility.is_cluster_database in statement

Question

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;

Answer 1

Starting with Oracle 12c, this could also be solved using the extended with clause, which allows PL/SQL functions to be defined in the declaration section:

with
function is_cluster_database return number
is
begin
  if dbms_utility.is_cluster_database then
    return 1;
  end if;
  return 0;
end;
select is_cluster_database from dual;

Alternative version using sys.diutil.bool_to_int to convert from datatype boolean to number:

with
function is_cluster_database return number
is
begin
  return sys.diutil.bool_to_int(dbms_utility.is_cluster_database);
end;
select is_cluster_database from dual;

Each of these two statements returns either 1 in the case of a clustered database (RAC) or 0 otherwise (single instance).

Answer 2

As suggest @MaxU you can create wrapper function.

create or replace function is_cluster_database return number is
begin
  return sys.diutil.bool_to_int(dbms_utility.is_cluster_database);
end is_cluster_database;

And then used it like this

select is_cluster_database from dual;

Of course, result be number type.

is_cluster_database

0

Answer 3

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