Various Errors When Executing DDL on Streams Target After Custom Transformation and Multi-byte Charactersets Used (Doc ID 1299650.1)

Last updated on FEBRUARY 28, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

When replicating DDL statements between different multi-byte Oracle databases using Oracle Streams, and a custom transformation is implemented that manipulates the DDL text (such as that designed to change the schema name that the DDL is applied to), various errors in DBA_APPLY_ERROR may result depending upon the type of DDL, such as:

ORA-00933: SQL command not properly ended
ORA-02158: invalid CREATE INDEX option (for create index DDL)

and so forth. Printing out the error from DBA_APPLY_ERROR using the procedures in Note 405541.1: "Procedure to Print LCRs" reveals that the DDL statement has extra characters inserted at the end of it that are blank spaces interspersed with the same characters that comprise the last part of the DDL statement. For example, for an original DDL statement of:

CREATE INDEX STREAMS_TEST.TEST_DUMMY ON STREAMS_TEST.TEST(C2)
TABLESPACE USERS
STORAGE (INITIAL
1M
NEXT 1M MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4);


might result in something similar to the following in DBA_APPLY_ERROR (output of the print_transaction procedure):

ddl: CREATE INDEX STREAMS_TEST_2.TEST_DUMMY ON STREAMS_TEST.TEST(C2)
TABLESPACE
USERS
STORAGE (INITIAL
1M
NEXT 1M MAXEXTENTS 200 PCTINCREASE 0 FREELISTS 4
FREELIST GROUPS 4) 0 F R E E L I S T S 4 F R E E L I S T G R O U P S
4 )

Note the echoed text at the end with blank spaces in between each character.

Changes

This problem has been noted when using the search_n_replace_clob procedure between multi-byte databases (UTF8 in particular) as discussed in  the following note:

Note 783203.1: "How to Setup Custom Rule Based Transformation"

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