Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

BOOLEAN data types and conversion issues with VALUES (table values constructor) clause

Bob BrylaJul 3 2023

Both BOOLEAN data types and doing an INSERT with the VALUES clause (table values constructor) are new. The target table:

create table test_bool (id   number, tf   boolean);

This does not work:

insert into test_bool
  values
    (1,0),
    (2,'true');
ORA-01790: expression must have same datatype as corresponding expression

But doing the INSERTs one at a time does work:

insert into test_bool values (1,0);
insert into test_bool values (2,'true');

I can theorize what is happening from the error message: but in the case of INSERTs into BOOLEAN columns, both ‘true’ and 0 are valid values for the column. Is the order of operations wrong or are assumptions being made about the column, not knowing the datatype of the target column?

This post has been answered by Jonathan Lewis on Jul 4 2023
Jump to Answer
Comments
Post Details
Added on Jul 3 2023
3 comments
871 views