My Oracle Support Banner

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

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.1 [Release 11.1 to 11.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
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

---------

<DATABASE_NAME>

 

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_ADMIN> cascade;

User dropped.

 

SQL> CREATE USER "<CDC_ADMIN>" PROFILE "DEFAULT"

2 IDENTIFIED BY "<PASSWORD>" DEFAULT TABLESPACE "USERS"

3 TEMPORARY TABLESPACE "TEMP"

4 QUOTA UNLIMITED

5 ON "USERS"

6 ACCOUNT UNLOCK;

 

User created.

 

SQL> GRANT CREATE SESSION TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT CREATE TABLE TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT CREATE TABLESPACE TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT UNLIMITED TABLESPACE TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT SELECT_CATALOG_ROLE TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT EXECUTE_CATALOG_ROLE TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT ALL ON sh.sales TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT ALL ON sh.products TO <CDC_ADMIN>;

Grant succeeded.

 

SQL> GRANT EXECUTE ON DBMS_CDC_PUBLISH TO <CDC_ADMIN>;

Grant succeeded.

 

SQL>

SQL> connect <CDC_ADMIN>/<PASSWORD>

Connected.

 

SQL> Create table <TABLE> (c1 varchar2(1000));

 

Table created.

 

SQL> EXECUTE dbms_capture_adm.prepare_table_instantiation ( '<CDC_ADMIN>.<TABLE>' );

 

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('<CHANGE_SOURCE_NAME>',><DESCRIPTION>','<SOURCE_DATABASE>',<FIRST_SCN>);

 

PL/SQL procedure successfully completed.

 

SQL> EXECUTE DBMS_CDC_PUBLISH.CREATE_CHANGE_SET('<CHANGE_SET_NAME>', <DESCRIPTION>','<CHANGE_SOURCE_NAME>','Y');

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( '<CDC_ADMIN>', '<CHANGE_TABLE_NAME>', '<CHANGE_SET_NAME>'

3 , '<CDC_ADMIN>', '<TABLE>'

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 => '<CHANGE_SET_NAME>', enable_capture => 'Y');

 

PL/SQL procedure successfully completed.

 

SQL> conn <CDC_ADMIN>/<PASSWORD>

Connected.

SQL> insert into <TABLE> values('ABCD ');-- one space

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select capture_name,state from v$streams_capture;

 

CAPTURE_NAME

------------------------------

STATE

-------------------------------------------------------------------------

<CAPTURE_NAME>

CAPTURING CHANGES

 

SQL> select apply_name,status from dba_apply;

 

APPLY_NAME STATUS

------------------------------ --------

 

<APPLY_NAME> ENABLED

 

SQL> select * from <CHANGE_TABLE_NAME>;

 

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

 

COLUMN1

--------------------------------------------------------------------------------

ABCD #

 

SQL> select c1||'#' as column1 from <CHANGE_TABLE_NAME>;

 

COLUMN1

--------------------------------------------------------------------------------

ABCD#

 

SQL> insert into <TABLE> values('ABCD ');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select c1||'#' as column1 from <CHANGE_TABLE_NAME>;

 

COLUMN1

--------------------------------------------------------------------------------

ABCD#

ABCD#

 

SQL> spool off

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.