SPACES AUTO TRIMMED FROM VARCHAR2 COLUMN OF THE CDC CHANGE TABLE (Doc ID 1285849.1)

Last updated on MAY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.1 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms


All-blank strings,spaces like ' ' are truncated in CDC change tables.
This can either show as logically corrupt data in the CDC table, or may trigger constrain violation errors. eg: ORA-1400 "cannot insert NULL into ..." errors


Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.



Sample Test Steps:

SQL> conn / as sysdba
Connected.
SQL> select name from v$database;

NAME
---------
DB2

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Database altered.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> drop user CDC_ADMIN11 cascade;

User dropped.

SQL> cREATE USER "CDC_ADMIN11" PROFILE "DEFAULT"
2 IDENTIFIED BY "new*pass" DEFAULT TABLESPACE "USERS"
3 TEMPORARY TABLESPACE "TEMP"
4 QUOTA UNLIMITED
5 ON "USERS"
6 ACCOUNT UNLOCK;

User created.

SQL> GRANT CREATE SESSION TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT CREATE TABLE TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT CREATE TABLESPACE TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT ALL ON sh.sales TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT ALL ON sh.products TO CDC_ADMIN11;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_CDC_PUBLISH TO CDC_ADMIN11;

Grant succeeded.

SQL>
SQL> connect CDC_ADMIN11/new*pass
Connected.
SQL> Create table SAFA_SIL (c1 varchar2(1000));

Table created.

SQL> EXECUTE dbms_capture_adm.prepare_table_instantiation ( 'CDC_ADMIN11.SAFA_SIL' );

PL/SQL procedure successfully completed.

SQL> connect / as sysdba
Connected.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_logminer_scn NUMBER;
3 BEGIN
4 dbms_capture_adm.build ( l_logminer_scn );
5 DBMS_OUTPUT.PUT_LINE('SCN: '|| l_logminer_scn);
6 END;
7 /
SCN: 690205

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE('DW1','Source Database is DB2','DB2',690205);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_CDC_PUBLISH.CREATE_CHANGE_SET('CS','Source Database is DB1 with change set CS05','DW1','Y');

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( 'CDC_ADMIN11', 'CTBL_SAFA_SIL', 'CS'
3 , 'CDC_ADMIN11', 'SAFA_SIL'
4 , 'c1 varchar2(1000)'
5 , 'NEW', 'Y', 'N'
6 , 'N', 'Y', 'N', 'N'
7 , 'Y', 'TABLESPACE USERS' );
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_cdc_publish.alter_change_set (change_set_name => 'CS', enable_capture => 'Y');

PL/SQL procedure successfully completed.

SQL> conn CDC_ADMIN11/new*pass
Connected.
SQL> insert into SAFA_SIL values('ABCD ');-- one space

1 row created.

SQL> commit;

Commit complete.

SQL> select capture_name,state from v$streams_capture;

CAPTURE_NAME
------------------------------
STATE
-------------------------------------------------------------------------
CDC$C_CS
CAPTURING CHANGES

SQL> select apply_name,status from dba_apply;

APPLY_NAME STATUS
------------------------------ --------

CDC$A_CS ENABLED

SQL> select * from CTBL_SAFA_SIL;

OP CSCN$ COMMIT_TI XIDUSN$ XIDSLT$ XIDSEQ$ RSID$ TIMESTAMP
-- ---------- --------- ---------- ---------- ---------- ---------- ---------
TARGET_COLMAP$
--------------------------------------------------------------------------------
C1
--------------------------------------------------------------------------------
I 707321 11-SEP-10 10 39 355 1 11-SEP-10
FE070000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
ABCD


SQL> select c1||'#' as column1 from SAFA_SIL;

COLUMN1
--------------------------------------------------------------------------------
ABCD #

SQL> select c1||'#' as column1 from CTBL_SAFA_SIL;

COLUMN1
--------------------------------------------------------------------------------
ABCD#

SQL> insert into safa_sil values('ABCD ');

1 row created.

SQL> commit;

Commit complete.

SQL> select c1||'#' as column1 from CTBL_SAFA_SIL;

COLUMN1
--------------------------------------------------------------------------------
ABCD#
ABCD#

SQL> spool off

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms