Oracle Database 23c introduced the SQL boolean data type which stores the values ‘TRUE’ or ‘FALSE’ in the boolean columns in the table. We can use the keywords BOOLEAN or BOOL in the CREATE TABLE or ALTER TABLE statement.
We can use literals to represent “TRUE” and “FALSE” state in columns. Case is not enforced in “TRUE” and “FALSE” literals used in the columns and we can have all lower case, all upper case, or a combination of upper and lower case.
Literals like ‘YES’ or ‘Y’ or ‘y’ or ‘TRUE’ or ‘T’ can be used to represent TRUE.
Numbers can also be used in place of literals. 0 translates to FALSE. Non 0 values like 1 or 2 or even -1 translate to TRUE.
In this post we will use the existing GoldenGate 23c Pipeline created in the earlier tutorial for replication of the MYOBJECTS table with a BOOLEAN data type column.
Alter the source MYOBJECTS table to add IS_ARCHIVED column with the BOOLEAN datatype.
[oracle@db23c FREE]$ sqlplus demo/Oracle_4U@freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Thu Oct 19 22:50:30 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Tue Oct 17 2023 23:35:01 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> desc myobjects Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_ID NOT NULL NUMBER OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) OBJECT_TYPE VARCHAR2(23) SQL> alter table myobjects add is_archived BOOLEAN; Table altered. SQL> desc myobjects Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_ID NOT NULL NUMBER OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) OBJECT_TYPE VARCHAR2(23) IS_ARCHIVED BOOLEAN
Alter the target MYOBJECTS table to add IS_ARCHIVED column with the BOOLEAN datatype.
[oracle@db23c FREE]$ sqlplus demo/Oracle_4U@freepdb2 SQL*Plus: Release 23.0.0.0.0 - Production on Thu Oct 19 22:50:30 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Tue Oct 17 2023 23:35:01 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> alter table myobjects add is_archived BOOLEAN; Table altered.
Update the IS_ARCHIVED column. The literal value ‘YES’ translates to represent a TRUE value in the column.
[oracle@db23c FREE]$ sqlplus demo/Oracle_4U@freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Fri Oct 20 03:19:19 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Fri Oct 20 2023 03:18:31 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> update myobjects set IS_ARCHIVED='YES' where owner='SYSTEM'; 52482 rows updated. SQL> commit; Commit complete.
Connect to the target FREEPDB2 database database and verify that the the IS_ARCHIVED column has been updated.
[oracle@db23c FREE]$ sqlplus demo/Oracle_4U@freepdb2
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Oct 20 03:35:07 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Oct 19 2023 22:55:35 -04:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> select count(*) from myobjects where IS_ARCHIVED;
COUNT(*)
----------
52482
View the Pipeline Extract and Replicat process statistics.
Next the IS_ARCHIVED column is updated using the value ‘0’ – this translates to represent a value of FALSE.
SQL> update myobjects set IS_ARCHIVED=0 where owner='HR';
34 rows updated.
SQL> commit;
Commit complete.
SQL> select distinct IS_ARCHIVED from myobjects where owner='HR';
IS_ARCHIVED
-----------
FALSE
SQL> select count(*) from myobjects where IS_ARCHIVED;
COUNT(*)
----------
52482
SQL> select count(*) from myobjects where not IS_ARCHIVED;
COUNT(*)
----------
34
View the Pipeline Operations graphs for both the Extract as well as Replicat which shows the replication statistics for the 34 rows with the BOOLEAN data type on the source MYOBJECTS table which were updated.
